Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.