Postgresql - Filter object array and extract required values in a json object-CodePudding

I have a PostgreSQL table like below:

| data |
| -------------- |
| {"name":"a","tag":[{"type":"country","value":"US"}]} |
| {"name":"b","tag":[{"type":"country","value":"US"}]}, {"type":"country","value":"UK"}]} |
| {"name":"c","tag":[{"type":"gender","value":"male"}]} |

The goal is to extract all the value in "tag" array with "type" = "country" and aggregate them into a text array. The expected result is as follows:

| result         |
| -------------- |
| ["US"]         |
| ["US", "UK"]   |
| []             |

I've tried to expand the "tag" array and aggregate the desired result back; however, it requires a unique id to group up the results. Hence, I add a column with row number to serve as unique id. Here is what I've done:

SELECT ROW_NUMBER() OVER () AS id, * INTO data_table_with_id FROM data_table;

SELECT ARRAY_AGG(tag_value) AS result
FROM (
    SELECT
        id, 
        json_array_elements("data"::json->'tag')->>'type' as tag_type,
        json_array_elements("data"::json->'tag')->>'value' as tag_value 
    FROM data_table_with_id
) tags 
WHERE tag_type = 'country' 
GROUP BY id;

Is it possible to use a single select to filter the object array and get the required results?

CodePudding user response：

You can do this easily with a JSON path function:

select jsonb_path_query_array(data, '$.tag[*] ?(@.type == "country").value')
from data_table;