CODEWITHSUNDEEP
postgresqlpostgresql-json
Siva Arunachalam
Siva Arunachalam

Reputation: 7740

PostgreSQL JSONB Array - Find if a key exists

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.

Upvotes: 2

Views: 7507

Answers (2)

Dmitry
Dmitry

Reputation: 7266

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

Upvotes: 4

NM Pennypacker
NM Pennypacker

Reputation: 6942

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.

Upvotes: 3

Related Questions