PostgreSQL/JSON

From Chorke Wiki
Revision as of 04:51, 16 January 2026 by Shahed (talk | contribs)
Jump to navigation Jump to search

Query

Query 

{
    "type": "Reseller",
    "commission": {
        "type": "Percentage",
        "value": "25"
    }
}

SELECT extended_properties ->'commission'->'value'
FROM seller WHERE extended_properties ->>'type' = 'Reseller';
--
UPDATE seller
SET extended_properties = jsonb_set(extended_properties, '{type}', '"Retailer"')
WHERE id = 1000;
--
UPDATE seller
SET extended_properties = extended_properties || jsonb_build_object('code', code)
WHERE id = 1000;
--
UPDATE seller
SET extended_properties = jsonb_set(extended_properties, '{commission, type}', '"Bonus"')
WHERE id = 1000 AND extended_properties ->> 'type' = 'Reseller';
JSON JSONB Result 
SELECT id, name FROM json_to_record (
    '{"id":1,"name":"Academia"}'::json
) AS concern(id int, name text);

SELECT id, name FROM jsonb_to_record (
    '{"id":1,"name":"Academia"}'::jsonb
) AS concern(id int, name text);

  id |   name   
 ----+----------
   1 | Academia

SELECT * FROM json_array_elements_text('[
    {"id":1,"name":"Academia"},
    {"id":2,"name":"Agronomy"},
    {"id":3,"name":"Software"}
]'::json);

SELECT * FROM jsonb_array_elements_text('[
    {"id":1,"name":"Academia"},
    {"id":2,"name":"Agronomy"},
    {"id":3,"name":"Software"}
]'::jsonb);

              value             
 -------------------------------
  {"id": 1, "name": "Academia"}
  {"id": 2, "name": "Agronomy"}
  {"id": 3, "name": "Software"}

SELECT id, name FROM json_array_elements_text('[
    {"id": 1, "name": "Academia"},
    {"id": 2, "name": "Agronomy"},
    {"id": 3, "name": "Software"}
]'::json) AS concerns_text,
json_to_record(concerns_text::json)
AS concerns_json(id int, name text);

SELECT id, name FROM jsonb_array_elements_text('[
    {"id": 1, "name": "Academia"},
    {"id": 2, "name": "Agronomy"},
    {"id": 3, "name": "Software"}
]'::jsonb) AS concerns_text,
jsonb_to_record(concerns_text::jsonb)
AS concerns_json(id int, name text);

  id |   name   
 ----+----------
   1 | Academia
   2 | Agronomy
   3 | Software
 (3 rows)

SELECT id, name FROM json_array_elements('[
    {"id": 1, "name": "Academia"},
    {"id": 2, "name": "Agronomy"},
    {"id": 3, "name": "Software"}
]'::json) AS concerns_array,
json_to_record(concerns_array)
AS concerns_rows(id int, name text);

SELECT id, name FROM jsonb_array_elements('[
    {"id": 1, "name": "Academia"},
    {"id": 2, "name": "Agronomy"},
    {"id": 3, "name": "Software"}
]'::jsonb) AS concerns_array,
jsonb_to_record(concerns_array)
AS concerns_rows(id int, name text);

  id |   name   
 ----+----------
   1 | Academia
   2 | Agronomy
   3 | Software
 (3 rows)

SELECT id, name FROM json_to_recordset('{"data": [
    {"concerns": {"id": 1, "name": "Academia"}},
    {"concerns": {"id": 2, "name": "Agronomy"}},
    {"concerns": {"id": 3, "name": "Software"}}
]}'::json -> 'data') as r(concerns json),
json_to_record(concerns) AS concern(id int, name text);

SELECT id, name FROM jsonb_to_recordset('{"data": [
    {"concerns": {"id": 1, "name": "Academia"}},
    {"concerns": {"id": 2, "name": "Agronomy"}},
    {"concerns": {"id": 3, "name": "Software"}}
]}'::jsonb -> 'data') as r(concerns jsonb),
jsonb_to_record(concerns) AS concern(id int, name text);

  id |   name   
 ----+----------
   1 | Academia
   2 | Agronomy
   3 | Software
 (3 rows)

Insert

Insert 

-- query » group : religion
-- -----------------------------------------------------------------------------
-- SELECT MAX(id) FROM grouped_collection;
-- SELECT *       FROM grouped_collection WHERE group_name = 'religion';
-- DELETE         FROM grouped_collection WHERE group_name = 'religion';
-- -----------------------------------------------------------------------------
WITH mixed_grouped_collection AS (
    SELECT
        cast('chorke.org' AS varchar) "domain",
        cast('religion'   AS varchar) "group_name",
        cast(raw_grouped_collection->>0 AS varchar) "code",
        cast(raw_grouped_collection->>1 AS varchar) "name",
        cast(raw_grouped_collection->>2 AS jsonb  ) "extended_properties",
        cast(raw_grouped_collection->>3 AS numeric) "order_coll"
    FROM jsonb_array_elements('[
        ["I", "Islam",     {}, 1],
        ["B", "Buddhist",  {}, 2],
        ["H", "Hindu",     {}, 3],
        ["C", "Christian", {}, 4],
        ["O", "Others",    {}, 5]
    ]'::jsonb) AS raw_grouped_collection
)
, new_grouped_collection AS (
    SELECT
        ROW_NUMBER() OVER() "id", LOCALTIMESTAMP "created_on",
        mgc.domain,  mgc.code, mgc.name, mgc.group_name,
        mgc.extended_properties, mgc.order_coll
    FROM mixed_grouped_collection mgc
    --  prevent old duplicate
    WHERE NOT EXISTS (
        SELECT id FROM grouped_collection
        WHERE (code, group_name, domain) = (mgc.code, mgc.group_name, mgc.domain)
    )
)
SELECT ngc.* FROM new_grouped_collection ngc;
-- INSERT INTO grouped_collection (id, created_on, domain, code, name, group_name, extended_properties, order_coll)
-- SELECT (SELECT (COALESCE(MAX(id), 0) + ngc.id) FROM grouped_collection),
--     ngc.created_on, ngc.domain, ngc.code, ngc.name, ngc.group_name,
--     ngc.extended_properties, ngc.order_coll
-- FROM new_grouped_collection ngc
-- --  prevent new duplicate
-- WHERE NOT EXISTS (
--     SELECT id FROM grouped_collection
--     WHERE (code, group_name, domain) = (ngc.code, ngc.group_name, ngc.domain)
-- )
-- ORDER BY (ngc.order_coll, ngc.id) ASC;

References

References

Retrieved from "https://wiki.chorke.org/index.php?title=PostgreSQL/JSON&oldid=16134"