Search code examples
sqlpostgresqldatabase-designknex.jsjsonb

Query for key in an element of a JSON array

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

enter image description here

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?

Solution

  • 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:

    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: