CODEWITHSUNDEEP
sqljsonselectpresto
Daniel Miranda
Daniel Miranda

Reputation: 33

Why json_extract works but json_extract_scalar does not?

I have a dataset containing a column in json with an attribute giving me a list, and I would like to unnest it to join some different data.

I thought about json_extract_scalar the json_data, then I could split it and finally unnest with other operations, however I got a problem.

In my case, when i run the json_extract it works fine but i cannot convert to a varchar. In the other hand, if i use json_extract_scalar it returns a null value.

I think the problem should be the quotation marks, but I am not sure how to deal with it - and even if this is the correct problem.

Let me give you a sample of the data:

{"my_test_list":["756596263-0","743349523-371296","756112380-0","755061590-0"]}

Can you guys give me some advice?

I'm querying SQL in Presto.

Upvotes: 1

Views: 3001

Answers (1)

GMB
GMB

Reputation: 222722

What you are storing under key my_test_list is a JSON array, not a scalar value - which is why json_extract_scalar() returns null.

It is rather unclear how you want to use that data. A typical solution is to cast it to an array, that you can then use as needed, for example by unnesting it. The base syntax would be:

cast(json_extract(mycol, '$.my_test_list') as array(varchar))

You would then use that in a lateral join, like:

select t.mycol, x.myval
from mytable t
cross join unnest(
    cast(json_extract(mycol, '$.my_test_list') as array(varchar))
) as x(myval)

Upvotes: 1

Related Questions