I want to return Germany and UK from the below json column/string.
{
"id": 1,
"name": "phone",
"components": [
{
"id": 1,
"type": "screen",
"manufacturers": [
{
"id": 1,
"name": "a",
"country": "Germany"
}
]
},
{
"id": 2,
"type": "keyboard",
"manufacturers": [
{
"id": 1,
"name": "a",
"country": "UK"
}
]
}
]
}
Using a JSONPATH query, that should be simple:
SELECT * FROM jsonb_path_query('your JSON string', 'strict $.**.country');
jsonb_path_query
══════════════════
"Germany"
"UK"
(2 rows)
TL;DR:
select
json_array_elements(
json_array_elements(
json->'components'
)->'manufacturers'
)->>'country' as component_manufacturer_country
from foo
See 9.16. JSON Functions and Operators.
From the inmost part of the expression to the outer:
-> indexes to the components attributes as jsoncomponents is an array, we split to rows using json_array_elements()manufacturers attribute->> indexes to the country attribute as text