Find if a key exists or not - PostgreSQL JSONB Array
Records for data columns
Record 1:
[
{ "KEY1": "VALUE1", "KEY2": "VALUE2" },
{ "KEY1": "VALUE3", "KEY3": "VALUE4" },
]
Record 2:
[
{ "KEY1": "VALUE1", "KEY2": "VALUE2" },
{ "KEY3": "VALUE3", "KEY4": "VALUE4" },
]
create table fruits
(
id serial not null,
data jsonb
);
SELECT id, arr_elem
FROM fruits AS fruit, jsonb_array_elements(
(
SELECT data
FROM test_table
WHERE id = fruit.id
)
) AS arr_elem
WHERE arr_elem#>'{KEY4}' IS NOT NULL
