Query nested json columns in postgresql

Question

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"
 }
 ]
 }
 ]
}

• Akina: Tips for asking a good Structured Query Language (SQL) question, #5 and #3.
• Community: Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

Answer 1

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)

Answer 2

TL;DR:

select 
 json_array_elements(
 json_array_elements(
 json->'components'
 )->'manufacturers'
 )->>'country' as component_manufacturer_country
from foo

_fiddle

See 9.16. JSON Functions and Operators.

From the inmost part of the expression to the outer:

1. -> indexes to the components attributes as json
2. because components is an array, we split to rows using json_array_elements()
3. repeat steps 1 & 2 with the manufacturers attribute
4. ->> indexes to the country attribute as text