Hoe zet je een json-array om in rijen in postgres

Ik heb een json-array opgeslagen in mijn postgres-database.
De json ziet er als volgt uit:

[
    {
        "operation": "U",
        "taxCode": "1000",
        "description": "iva description",
        "tax": "12"
    },
    {
        "operation": "U",
        "taxCode": "1001",
        "description": "iva description",
        "tax": "12"
    },
    {
        "operation": "U",
        "taxCode": "1002",
        "description": "iva description",
        "tax": "12"
    }
]

Nu moet ik de array SELECTzodat elk element in een andere rij van het queryresultaat staat. Dus de SELECT-instructie die ik uitvoer, moet de gegevens op deze manier retourneren:

data
--------------------------------------------------------------------------------------
{ "operation": "U", "taxCode": "1000", "description": "iva description", "tax":"12"}
{ "operation": "U", "taxCode": "1001", "description": "iva description", "tax":"12"}
{ "operation": "U", "taxCode": "1002", "description": "iva description", "tax":"12"}

Ik heb geprobeerd de functie unnest()te gebruiken

SELECT unnest(json_data::json)
FROM my_table

maar het accepteert het type jsonbniet.

Antwoord 1, autoriteit 100%

Ik plaats het antwoord dat oorspronkelijk door pozs is geschreven in het commentaargedeelte.

unnest()is voor de arraytypes van PostgreSQL.

In plaats daarvan kan een van de volgende functies worden gebruikt:

• json_array_elements(json)(9.3+)
• jsonb_array_elements(jsonb)(9.4+)
• json[b]_array_elements_text(json[b])(9.4+)

Voorbeeld:

select * from json_array_elements('[1,true, [2,false]]')

uitvoerwaarde

-------------
 | 1         |
 -------------
 | true      |
 -------------
 | [2,false] |
 -------------

Hierwaar de documentatie voor v9.4 kan zijn gevonden.

Antwoord 2, autoriteit 51%

Moeilijker voorbeeld:

Stel dat je een tabel hebt met rijen die elk een jsonb-array bevatten en dat je al die arrays wilt splitsen (of uit elkaar wilt halen) en wat geaggregeerde berekeningen wilt uitvoeren voor records die erin staan.

Tabel (laat het categorieszijn):

id | specifics (jsonb)
-----------------------------------------------------------------------------------
  1 | [{"name": "Brand", "required": true}, {"name": "Color", "required": false}]
  2 | [{"name": "Brand", "required": false}, {"name": "Color", "required": false}]

Dus als je wilt tellen hoeveel specifieke details je hebt, moet je een dergelijke zoekopdracht gebruiken:

SELECT specs.name, COUNT(*) AS total
FROM 
  categories, 
  jsonb_to_recordset(categories.specifics) AS specs(name jsonb, required boolean)
WHERE 
  specs.required = TRUE
  -- AND any other restrictions you need
GROUP BY specs.name
ORDER BY total DESC;

Hier FROM x, function(x.column)is een verkorte vorm van een laterale samenvoegingdie effectief elke rij uit categoriessamenvoegt met een virtuele tabel gemaakt door de functie jsonb_to_recordsetvan jsonb-array in diezelfde rij.

En het resultaat zal zijn:

name  | total
---------------
 Brand |     1

Link naar DB Fiddle: https://www.db-fiddle.com/f /c4xZcEgg9dsPVDtE7Keovv/0

Antwoord 3, autoriteit 44%

Ik raad je aan om in jouw geval de opdracht json_to_recordset te gebruiken. Uw SQL zou dan moeten zijn:

select *
from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as x("operation" text, "taxCode" int);

De uitvoer is:

------------------------
|   |operation|taxCode |
------------------------
| 1 |   "U"   |   1000 |
------------------------
| 2 |   "U"   |  10001 |
------------------------

De kolommen (of JSON-sleutels) van het voorbeeld kunnen vrij verder worden uitgebreid.