Extracting the same values from a JSON object

I have a problem with taking all values under the “id_ref_directory” keys.

I have a temp table:

CREATE LOCAL TEMP TABLE parsed_json(
    var_key text, 
    var_value text NOT NULL)
ON COMMIT DROP;

I prase a JSON into temp table:

INSERT INTO parsed_json(var_key, var_value)
SELECT * 
FROM json_each_text(var_in_json::json)
WHERE value IS NOT NULL and LENGTH(value) != 0;

I have var int[]:

var_territory_list := (SELECT var_value FROM parsed_json WHERE var_key = 'territory_custom');

And I have this value under “id_ref_directory” key:

[{"name_directory": "ЯПОНИЯ", "id_ref_directory": 38},
{"name_directory": "ЯПОН", "id_ref_directory": 39}, 
{"name_directory": "ЯП", "id_ref_directory": 40}]}

I need to write into var_territory_listthis from JSON above (values of keys “id_ref_directory”):

38, 39, 40 -- Like an array (int[])

I thought that I could solve everything with simple operators like #> but I failed. Maybe it is necessary to do it through a cycle? Thank you in advance!

Answer

You can extract the array into rows:

postgres=# SELECT j.jsontext->'id_ref_directory' FROM (SELECT json_array_elements('[{"name_directory": "ЯПОНИЯ", "id_ref_directory": 38},
{"name_directory": "ЯПОН", "id_ref_directory": 39}, 
{"name_directory": "ЯП", "id_ref_directory": 40}]'::json) jsontext) as j;
 ?column? 
----------
 38
 39
 40
(3 rows)

If you really need an array, you can wrap it in an ARRAY() call:

postgres=# SELECT ARRAY(SELECT j.jsontext->'id_ref_directory' FROM (select json_array_elements('[{"name_directory": "ЯПОНИЯ", "id_ref_directory": 38},
{"name_directory": "ЯПОН", "id_ref_directory": 39}, 
{"name_directory": "ЯП", "id_ref_directory": 40}]'::json) jsontext) as j);
   array    
------------
 {38,39,40}
(1 row)

Disclosure: I work for EnterpriseDB (EDB)