Find if a key exists or not - PostgreSQL JSONB Array

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