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
Advertisement
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)