PostgreSQL/JSON: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
← Older edit
VisualWikitext
No edit summary
No edit summary
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
==Query==
==Query==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='3'|
!scope='col' style='text-align:left' colspan='3'|
Query
Query
|-
|-
|valign='top' style='width:33%'|
|valign='top' style='width:33%'|
<syntaxhighlight style='margin:3px 0' lang='json'>
<syntaxhighlight lang='json'>
{
{
     "type": "Reseller",
     "type": "Reseller",
Line 21: Line 21:
|-
|-
|valign='top' colspan='3'|
|valign='top' colspan='3'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT extended_properties ->'commission'->'value'
SELECT extended_properties ->'commission'->'value'
FROM seller WHERE extended_properties ->>'type' = 'Reseller';
FROM seller WHERE extended_properties ->>'type' = 'Reseller';
Line 41: Line 41:
|-
|-
|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM json_to_record (
SELECT id, name FROM json_to_record (
     '{"id":1,"name":"Academia"}'::json
     '{"id":1,"name":"Academia"}'::json
Line 48: Line 48:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM jsonb_to_record (
SELECT id, name FROM jsonb_to_record (
     '{"id":1,"name":"Academia"}'::jsonb
     '{"id":1,"name":"Academia"}'::jsonb
Line 55: Line 55:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='text'>
<syntaxhighlight lang='text'>
   id |  name   
   id |  name   
  ----+----------
  ----+----------
Line 62: Line 62:
|-
|-
|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT * FROM json_array_elements_text('[
SELECT * FROM json_array_elements_text('[
     {"id":1,"name":"Academia"},
     {"id":1,"name":"Academia"},
Line 71: Line 71:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT * FROM jsonb_array_elements_text('[
SELECT * FROM jsonb_array_elements_text('[
     {"id":1,"name":"Academia"},
     {"id":1,"name":"Academia"},
Line 80: Line 80:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='text'>
<syntaxhighlight lang='text'>
               value             
               value             
  -------------------------------
  -------------------------------
Line 89: Line 89:
|-
|-
|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM json_array_elements_text('[
SELECT id, name FROM json_array_elements_text('[
     {"id": 1, "name": "Academia"},
     {"id": 1, "name": "Academia"},
Line 100: Line 100:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM jsonb_array_elements_text('[
SELECT id, name FROM jsonb_array_elements_text('[
     {"id": 1, "name": "Academia"},
     {"id": 1, "name": "Academia"},
Line 111: Line 111:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='text'>
<syntaxhighlight lang='text'>
   id |  name   
   id |  name   
  ----+----------
  ----+----------
Line 121: Line 121:
|-
|-
|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM json_array_elements('[
SELECT id, name FROM json_array_elements('[
     {"id": 1, "name": "Academia"},
     {"id": 1, "name": "Academia"},
Line 132: Line 132:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM jsonb_array_elements('[
SELECT id, name FROM jsonb_array_elements('[
     {"id": 1, "name": "Academia"},
     {"id": 1, "name": "Academia"},
Line 143: Line 143:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='text'>
<syntaxhighlight lang='text'>


   id |  name   
   id |  name   
Line 154: Line 154:
|-
|-
|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM json_to_recordset('{"data": [
SELECT id, name FROM json_to_recordset('{"data": [
     {"concerns": {"id": 1, "name": "Academia"}},
     {"concerns": {"id": 1, "name": "Academia"}},
Line 164: Line 164:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
SELECT id, name FROM jsonb_to_recordset('{"data": [
SELECT id, name FROM jsonb_to_recordset('{"data": [
     {"concerns": {"id": 1, "name": "Academia"}},
     {"concerns": {"id": 1, "name": "Academia"}},
Line 174: Line 174:


|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='text'>
<syntaxhighlight lang='text'>


   id |  name   
   id |  name   
Line 186: Line 186:


==Insert==
==Insert==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left'|
!scope='col' style='text-align:left'|
Insert
Insert
|-
|-
|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
-- query » group : religion
-- query » group : religion
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
Line 242: Line 242:


==References==
==References==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='3'|
!scope='col' style='text-align:left' colspan='3'|
References
References
|-
|-
|valign='top' style='width:33%'|
|valign='top' style='width:33%'|
* [[PostgreSQL/Large Object|PostgreSQL » Large Object]]
* [[Dockerized PostgreSQL|PostgreSQL » Dockerized]]
* [[Dockerized PostgreSQL|PostgreSQL » Dockerized]]
* [[PostgreSQL/PgBouncer|PostgreSQL » PgBouncer]]
* [[PostgreSQL/PgBouncer|PostgreSQL » PgBouncer]]
* [[PostgreSQL/PgLoader|PostgreSQL » PgLoader]]
* [[PostgreSQL/PgLoader|PostgreSQL » PgLoader]]
* [[PostgreSQL/DBLink|PostgreSQL » DBLink]]
* [[PostgreSQL/XML|PostgreSQL » XML]]
* [[PostgreSQL/XML|PostgreSQL » XML]]
* [[PostgreSQL]]
* [[PostgreSQL]]

Latest revision as of 20:49, 19 January 2026

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=16203"