Query for key in an element of a JSON array

Question

I assumed to have a jsonb column header_format in my PostgreSQL table (which turns out to be type json[], really) .

A column value looks like this:

{"{\"label\":\"SUPPLIER\",\"name\":\"supplier_name\",\"value\":\"VNS LTD\",\"show\":true,\"showPrint\":true,\"map\":\"\",\"input_type\":\"text\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"PRODUCT NAME\",\"name\":\"product_name\",\"value\":\"\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"INVOICE / DC #\",\"name\":\"invoice_no\",\"value\":\"cs/12/1222\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"INVOICE / DC DATE\",\"name\":\"invoice_date\",\"value\":\"2022-06-02\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"DATE_EXT\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"INVOICE QTY\",\"name\":\"invoice_qty\",\"value\":260,\"show\":true,\"showPrint\":true,\"input_type\":\"number\",\"map\":\"invoiceQty\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"No Need to Map\"}","{\"label\":\"IR #\",\"name\":\"ir\",\"value\":\"FY-23-09-1\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"GRN NO\",\"name\":\"grn_no\",\"value\":\"5032344565\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"GRN DATE\",\"name\":\"grn_date\",\"value\":\"2022-06-02\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"LAST_GR_DATE_EXT\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"R/M CODE\",\"name\":\"rmcode\",\"value\":\"CR11803C00MAN\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"EDS /Q /P #\",\"name\":\"eds\",\"value\":\"DE / EDS / 454\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"eds\",\"mapFrom\":\"product\",\"default\":true,\"note\":\"\"}","{\"label\":\"DATE\",\"name\":\"date\",\"value\":\"2022-09-13\",\"show\":true,\"showPrint\":true,\"input_type\":\"date\",\"map\":\"\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}","{\"label\":\"R/M\",\"name\":\"rm\",\"value\":\"DC01+LC-MA-RL\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"rm\",\"mapFrom\":\"product\",\"default\":true,\"note\":\"\"}","{\"label\":\"RECEIVED QTY\",\"name\":\"received_qty\",\"value\":260,\"show\":true,\"showPrint\":true,\"input_type\":\"number\",\"map\":\"invoiceQty\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"No Need to Map\"}","{\"label\":\"SHELF LIFE\",\"name\":\"shelf_life\",\"value\":\"20.11.2022\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"Shelf Life\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"No Need to Map\"}","{\"label\":\"MATERIAL TEXT\",\"name\":\"material_text\",\"value\":\"CP7B-DC01,1.18 x 31.5\",\"show\":true,\"showPrint\":true,\"input_type\":\"text\",\"map\":\"MATTEXT\",\"mapFrom\":\"header\",\"default\":true,\"note\":\"\"}"}

I am inserting format in header_format column:

[{
label:'1',
value:'abc',
other...
},
{
label:'2',
value:'aab',
other...
}]

I want a list of rows when it matches "{label:'supplier'}" in array datatype.

I tried the following methods:

Method 1:

Database.rawQuery('select * from qasformones  where header_format @> "{label:supplier}"')

Method 2:

Database.rawQuery('select * from qasformones  where header_format @> "[{label:supplier}]"')

I got the following error:

column "{label:supplier}" does not exist

select * from qasformones where header_format @> "{label:supplier}" - column "{show:true}" does not exist

How to do this query?

Please clarify your question. Are you trying to use an array of values in your where clause? Then you can use the "in" keyword. But the question is a bit unclear. — Jurgen Rutten, Oct 06 '22 at 07:20
@JurgenRutten I am using array data type, I want to list out all the data when it match {label:"supplier"} in my array data type . (eg : where label=supplier) but i never used before array data type. — Balaji, Oct 06 '22 at 08:26
check https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-TABLE (see: jsonb ? text → boolean & jsonb ? text[] → boolean) these can be used to filter the results — Jurgen Rutten, Oct 06 '22 at 09:50
Clarify. Sorry these types of queries are new to me too. I'll experiment tomorrow. — Jurgen Rutten, Oct 06 '22 at 17:28
a) don't use `json[]`, use `jsonb` and store a json array (instead of a postgres array of json values) and b) use apostrophes (`'`) to delimit literal values, not quotes (`"`). — Bergi, Oct 08 '22 at 03:37
I think both json and jsonb are same,jsonb removes whitespace but not in json.my case can't do json to jsonb because of data already having in database. — Balaji, Oct 08 '22 at 04:24
Your question would attract more answers with a fiddle. Example: https://dbfiddle.uk/5lW4OAWU Always declare your version of Postgres and disclose the actual table definition in text form. Don't post images for text data. — Erwin Brandstetter, Oct 10 '22 at 02:51

Erwin Brandstetter · Accepted Answer · 2022-10-10T02:35:22.223

This query works for your current table design with an array of JSON values (json[]):

SELECT *
FROM   qasformones q
WHERE  EXISTS (
   SELECT FROM unnest(q.header_format) js
   WHERE  js->>'label' = 'SUPPLIER'   -- match case!
   );

But json[] is typically not what you want. Storage is inefficient, and this query is expensive and does not scale well. Consider transforming the column to jsonb containing an array or objects.

Fast path to sanity

ALTER TABLE is the fastest way - if you can afford to lock the table exclusively. See:

Set postgres column to not null with default without update

But it does not allow subquery expressions in the USING clause. So wrap the functionality in a quick temporary function. (Dropped automatically at the end of your session.)

CREATE FUNCTION pg_temp.jsb_transform (json[])
  RETURNS jsonb
  LANGUAGE sql AS
$func$
SELECT jsonb_agg(js) FROM unnest($1) js;
$func$;

ALTER TABLE qasformones
   ALTER COLUMN header_format TYPE jsonb USING pg_temp.jsb_transform(header_format);

Then I suggest:

VACUUM FULL ANALYZE qasformones;

And create an index:

CREATE INDEX qasformones_header_format_gin_idx ON qasformones USING gin (header_format jsonb_path_ops);

About that:

Now your query can be:

SELECT *
FROM   qasformones q
WHERE  header_format @> '[{"label":"SUPPLIER"}]';

Or, using SQL/JSON path expression in Postgres 12+

SELECT *
FROM   qasformones q
WHERE  header_format @? '$ ? (@.label == "SUPPLIER")';

fiddle

Regular table columns?

Your content looks highly regular. It may be more efficient, yet, to break out keys into dedicated regular table columns using jsonb_populate_recordset() See:

That would result in N rows for N array elements in your current json[]. Probably a one-to-many design. Related:

wow,it solved my problem within seconds thanks lot @Erwin Brandstetter — Balaji, Oct 10 '22 at 06:15
@ßãlãjî: See: https://stackoverflow.com/a/55448707/939860 and https://stackoverflow.com/a/24089729/939860 — Erwin Brandstetter, Oct 10 '22 at 12:52
getting error while insert invalid input type for json my input looks [] — Balaji, Oct 14 '22 at 10:46

Query for key in an element of a JSON array

1 Answers1

Fast path to sanity

Regular table columns?

Linked