Json extract coming as null – Presto SQL

I`m trying to use json extract to get some values using the code below:

json_extract_scalar(properties,'$.partner_target_app_id') as partner_target_app_id,

And it works, however the values are coming all as NULL . When I extract the properties field I get the values that I want, as the example below: {“allow_url”:false,”partner_target_app_id”:[28479748204829001,9388018374784],”link”:”http”}

Does anybody know what could be the issue here? I thought it was something related to the [] around the numbers but I also tried to use ‘$.[partner_target_app_id]’) and also got the same result.

thanks

Answer

json_extract_scalar is for extracting scalar values (numbers, strings, booleans). In your example, the value associated with partner_target_app_id is an array.

You can use json_extract, which, in this case, returns a value of type JSON containing a JSON array of numbers, and cast it to SQL array:

SELECT cast(json_extract(x, '$.partner_target_app_id') as array(bigint)) 
FROM (
    VALUES '{"allow_url":false,"partner_target_app_id":[28479748204829001,9388018374784],"link":"http"}') t(x);

               _col0
------------------------------------
 [28479748204829001, 9388018374784]
(1 row)