CODEWITHSUNDEEP
mysqlsqljson
JS_Riddler
JS_Riddler

Reputation: 1580

JSON extraction and casting to MySQL types

Is there a way to convert a JSON field into its corresponding "native" MySQL type, without explicitly casting it?

Suppose I have a table with these fields:

# table `test`
id INT UNSIGNED NOT NULL
json JSON NOT NULL

I insert some JSON, like this:

{field1: "abc", field2: 100}
{field1: "def", field2: 20}

I'd like to sort by field2, which is numeric, but without having to cast it. It seems like this should be possible, given that all field2 values are JSON numbers. Is this possible?

select * from test order by CAST(json->>'$.field2' as unsigned) desc
                            ^
                            └ Is there an alternative to doing this?

Upvotes: 1

Views: 50

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

You can use a tricky way(multiplication by one) in order to cast as number implicitly as an alternative case :

SELECT * 
  FROM test 
 ORDER BY json->>'$.field2'*1 DESC

Demo

Upvotes: 1

Related Questions