Extract values from JSON with nesting

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 have a JSON, in 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 JSON like this in “value” field:

[{"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 abowe (values of keys “id_ref_directory”):

[38, 39, 40]

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

Click: step-by-step demo:db<>fiddle

SELECT
    jsonb_agg(elems -> 'id_ref_directory')
FROM mytable,
    jsonb_array_elements(mydata -> 'territory_custom') AS elems

1. jsonb_array_elements() gives you one row per array element
2. elems -> 'id_ref_directory' gives you the expected values of these elements
3. jsonb_agg() aggregates them into a JSON array

If you want an int array:

SELECT
    array_agg((elems ->> 'id_ref_directory')::int)
FROM mytable,
    jsonb_array_elements(mydata -> 'territory_custom') AS elems

1. Instead of jsonb_agg() use array_agg() after casting the elements into int values

---

Postgres 12 allows to use JSONpath:

Click: demo:db<>fiddle

SELECT
    jsonb_path_query_array(mydata, '$.territory_custom.id_ref_directory') elems
FROM mytable

1. '$.territory_custom.id_ref_directory' gives you all expected elements
2. The function aggregates the result into an array

If you want an int array:

SELECT
    array_agg(elems::int)
FROM mytable,
    jsonb_path_query(mydata, '$.territory_custom.id_ref_directory') elems

1. Calculate all elements into one row per element
2. Aggregate these elements with array_agg() after casting them into int values