How to turn a json array into rows in postgresql

Question

I am trying to convert this result where I have a JSON type column, which comes from a sql query

{
  "rows": [
    {
      "columns": {
        "jseq": 1,
        "Nombre": "0000956_LANZADOR",
        "rutaEsquema": "AXIS",
        "TipoDeComponente": "SQL",
        "value": 0,
        "detalleDelComponente": "Solución incidente 956"
      }
    },
    {
      "columns": {
        "jseq": 2,
        "Nombre": "0000956_02_Borrar_Mandatos.sql",
        "rutaEsquema": "AXIS",
        "TipoDeComponente": "SQL",
        "value": 1,
        "detalleDelComponente": "Brecha 67"
      }
    }
  ]
}

to this

Nombre                     | rutaEsquema | TipoDeComponente | detalleDelComponente
---------------------------+-------------+------------------+-----------------------
0000956_LANZADOR           | AXIS        | SQL              | Solución incidente 956
0000956_02_Borrar_Mandatos | AXIS        | SQL              | Brecha 67

I am using Postgresql

"which comes from a sql query" - can you modify that query? If yes, please post it - sounds pretty unnecessary to create a JSON only to destructure it again. — Bergi
– Bergi, Commented Sep 26, 2019 at 21:14
The JSON that is returned from the query comes from a field that is generated by a Jira plugin whose result always comes in JSON format, it was not that I returned it JSON — Diego Valdes
– Diego Valdes, Commented Sep 27, 2019 at 12:50

Bergi · Accepted Answer · 2019-09-26 21:35:44Z

6

json_to_record and json_to_recordset from the JSON processing functions do exactly this. In your case:

SELECT cols.*
FROM json_to_recordset(yourJsonValue -> 'rows') AS rows(columns JSON),
     json_to_record(columns) AS cols(
       "Nombre" TEXT,
       "rutaEsquema" TEXT,
       "TipoDeComponente" TEXT,
       "detalleDelComponente" TEXT)

online demo

edited Sep 26, 2019 at 21:35

answered Sep 26, 2019 at 21:26

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2019-09-26 21:35:36Z

3

SELECT r."Nombre",
       r."rutaEsquema",
       r."TipoDeComponente",
       r."detalleDelComponente"
FROM jsonb_to_recordset(/* your JSONB value */ -> 'rows') AS q(columns jsonb)
   CROSS JOIN LATERAL
      jsonb_to_record(columns)
         AS r(jseq bigint,
              "Nombre" text,
              "rutaEsquema" text,
              "TipoDeComponente" text,
              value bigint,
              "detalleDelComponente" text
             );

answered Sep 26, 2019 at 21:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2019-09-26 21:57:00Z

One option would be using json_each function to expand the outermost JSON object into a set of key/value pairs, and then extract array elements by using json_array_elements :

select elm->>'Nombre' as Nombre,
       elm->>'rutaEsquema' as rutaEsquema,
       elm->>'TipoDeComponente' as TipoDeComponente,
       elm->>'detalleDelComponente' as detalleDelComponente
  from
  (
   select elms -> 'columns' as elm 
     from(
          select json_array_elements(js.value) as elms
            from tab,
                 json_each(js_col) as js
         ) q1
  ) q2

Demo

You can simplify that nested query to select json_array_elements(js_col -> 'rows') -> 'columns' as elm from tab

Śhāhēēd · Accepted Answer · 2021-08-13 11:52:27Z

Find the simplified JSON query, for nested JSON object. This query simplified for clarification and better understanding.

Query

SELECT id, name FROM json_to_recordset('{
    "rows": [{
        "columns": {"id": 1, "name": "Chorke"}
    }, {
        "columns": {"id": 2, "name": "Academia"}
    }, {
        "columns": {"id": 3, "name": "Inc."}
    }]
}'::json -> 'rows') as list(columns json),
json_to_record(columns) AS concern(id int, name text);

Results

 id |   name   
----+----------
  1 | Chorke
  2 | Academia
  3 | Inc.
(3 rows)

Collectives™ on Stack Overflow

How to turn a json array into rows in postgresql

4 Answers 4

Comments

Comments

1 Comment

Query

Results

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Query

Results

Comments

Linked

Related