Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE qasformones(id int, header_format JSON[]);
INSERT INTO qasformones VALUES
  (1, '{"{\"label\":\"NOT 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\":\"\"}"}')
, (2, '{"{\"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\":\"\"}"}')
;

CREATE TABLE

INSERT 0 2

TABLE qasformones;

id
header_format

1
{"{"label":"NOT 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":""}"}

2
{"{"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":""}"}

SELECT 2

-- query for existing design
SELECT *
FROM   qasformones q
WHERE  EXISTS (
   SELECT
   FROM   unnest(q.header_format) js
   WHERE  js->>'label' = 'SUPPLIER'
   );

id
header_format

2
{"{"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":""}"}

SELECT 1

-- sanitize table design
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);

CREATE FUNCTION

ALTER TABLE

VACUUM FULL ANALYZE qasformones;

VACUUM

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

CREATE INDEX

TABLE qasformones;

id
header_format

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

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

SELECT 2

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

id
header_format

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

SELECT 1

-- alternative with JSON/PATH expression
SELECT *
FROM   qasformones q
WHERE  header_format @? '$ ? (@.label == "SUPPLIER")';

id
header_format

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

SELECT 1

SET enable_seqscan = off;  -- just for demo with tiny table!

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

SET

QUERY PLAN

Bitmap Heap Scan on qasformones q  (cost=8.00..12.01 rows=1 width=861)

  Recheck Cond: (header_format @> '[{"label": "SUPPLIER"}]'::jsonb)

  ->  Bitmap Index Scan on qasformones_header_format_gin_idx  (cost=0.00..8.00 rows=1 width=0)

        Index Cond: (header_format @> '[{"label": "SUPPLIER"}]'::jsonb)

EXPLAIN

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

QUERY PLAN

Bitmap Heap Scan on qasformones q  (cost=8.00..12.01 rows=1 width=861)

  Recheck Cond: (header_format @? '$?(@."label" == "SUPPLIER")'::jsonpath)

  ->  Bitmap Index Scan on qasformones_header_format_gin_idx  (cost=0.00..8.00 rows=1 width=0)

        Index Cond: (header_format @? '$?(@."label" == "SUPPLIER")'::jsonpath)

EXPLAIN