PostgreSQL: Adding Where clause to nested JSONB query

Question

I'm trying to return the "value": "Brunswick" WHERE "language": "en" from the JSON string bellow

This returns the first in the list I don't know the index in the actual data

SELECT id, names->'common'->0->>'value' AS name FROM test;

Where as this returns 1, null

SELECT id,names @@ '$.common[*].value' AS en_name  FROM test
WHERE names @@ '$.common[*].language == "en"'

The above from here how to query for values in a deep nested json array in Postresql?

Also tried jsonb_path_query from Postgres find in jsonb nested array

Table with Cut down JSON string

    INSERT INTO test(id,names) VALUES(1,'{
  "short": null,
  "common": [
    {
      "value": "برونشوايغ",
      "language": "ar"
    },
    {
      "value": "Brunswick",
      "language": "en"
    }
  
  ]
}');

Here's my https://dbfiddle.uk/UiXTG8Vo

The plan is to update the en_name column with the returned value

"Also tried jsonb_path_query from Postgres find in jsonb nested array" - what exactly did you try? — Bergi
– Bergi, Commented Dec 2, 2023 at 12:36
At the bottom of the fiddle SELECT id,'common'->>'name_value' AS en_name, 'common'->>'language' AS language FROM test, JSONB_ARRAY_ELEMENTS(names->'common') AS common JSONB_ARRAY_ELEMENTS(common->'value') as name_value, JSONB_ARRAY_ELEMENTS(common->'language') AS language WHERE language = 'en'; — Holly
– Holly, Commented Dec 2, 2023 at 12:40

Bergi · Accepted Answer · 2023-12-02 12:42:56Z

1

You have only a single level of array nesting, so you need jsonb_array_elements only once:

SELECT id, name->>'value' AS en_name
FROM test, jsonb_array_elements(names->'common') AS name
WHERE name->>'language' = 'en';

The corresponding json path expression would be

SELECT id, jsonb_path_query(names, '$.common[*]?(@.language == "en").value') AS en_name
FROM test;

^{(Updated fiddle)}

answered Dec 2, 2023 at 12:42

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Holly Over a year ago

Thank you! The first is easier to read and the second is a 1 liner. Are there any advantages in using either approach?

Bergi Over a year ago

@Holly Not that I know.

Collectives™ on Stack Overflow

PostgreSQL: Adding Where clause to nested JSONB query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related