PostgreSQL JSONB Array - Find if a key exists

Question

The table Product has a jsonb column attributes in PostgreSQL 9.6 and contains the following entries for the attributes column.

Record 1:

[
  {    "KEY1": "VALUE1",    "KEY2": "VALUE2"  },
  {    "KEY1": "VALUE3",    "KEY3": "VALUE4"  },
]

Record 2:

[
  {    "KEY1": "VALUE1",    "KEY2": "VALUE2"  },
  {    "KEY3": "VALUE3",    "KEY4": "VALUE4"  },
]

How can we filter the records which contain the KEY4 key? The jsonb operators given here doesn't provide the details for jsonb arrays.

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

For table like this:

create table test_table
(
  id serial not null,
  data jsonb
);

Using your data

SELECT id, arr_elem
FROM test_table AS tt, jsonb_array_elements(
    (
      SELECT data
      FROM test_table
      WHERE id = tt.id
    )
) AS arr_elem
WHERE arr_elem#>'{KEY4}' IS NOT NULL

You basically unpack the array and join it back with the source table.

Result:

id , arr_elem

2 , {"KEY3": "VALUE3", "KEY4": "VALUE4"}

SQLFiddle

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 29, 2017 at 14:38

Dmitry

7,29614 gold badges42 silver badges42 bronze badges

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

Comments

NM Pennypacker · Accepted Answer · 2017-09-29 14:08:21Z

3

You'll have to substitute the correct table and column names, but this should do the trick:

FROM your_table jsonb_array_elements(your_table.the_array_key->'your_column') AS something WHERE something->>'KEY4'::text != 'null'

Hard to say for sure without knowing the table and column names. The bottom line is that you need to use json_array_elements.

edited Sep 29, 2017 at 14:08

answered Sep 29, 2017 at 13:51

NM Pennypacker

6,99211 gold badges38 silver badges41 bronze badges

6 Comments

Siva Arunachalam Over a year ago

Can you explain how jsonb_array_elements in the where condition?

NM Pennypacker Over a year ago

Updated the answer. When you save the jsonb AS something you can get what you need from the array. postgresql.org/docs/current/static/…

Siva Arunachalam Over a year ago

What is the_array_key here?

NM Pennypacker Over a year ago

Whatever the array is stored in. For example, if you have a table called posts and a column called comments, then it's jsonb_array_elements(posts.comments), but if your array is nested in a key then it's jsonb_array_elements(posts.comments->'whatever_the_array_key_is_called')

Siva Arunachalam Over a year ago

It is providing the syntax error. Can you update for your query in the answer?

|

Collectives™ on Stack Overflow

PostgreSQL JSONB Array - Find if a key exists

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related