PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres: Expand JSON data to columns and rows

I have a table in SQL containing a column in JSON format. Here is a sample of the SQL query output:

enter image description here

I need for each row in this table to expand the JSON objects and values into new columns with the respective data included.

Example for the first row would be one new column named "rate" and its value would be "0.25", while for the second row other columns should be created named "from","to","fixed" and the respective values should be stored in two separate rows keeping the rest of the info identical as is in the query itself.

The complexity here is each row on the dataset has different information stored in the "Data Value" column (JSON data).

Is there any way I could use a query or any other method to do this?

EDIT:
Adding my SELECT statement for reference adding a new column for each object, but this does not work for arrays.

    SELECT
    port.id                                                                         as "Port ID",
    port.name                                                                       as "Port Name",
    country.name                                                                    as "Country",
    port_expense.id                                                                 as "Port Expense ID",
    port_expense.name::text                                                         as "Port Expense",
    terminal.id                                                                     as "Terminal ID",
    terminal.name                                                                   as "Terminal Name",
    berth.id                                                                        as "Berth ID",
    berth.name                                                                      as "Berth Name",
    port_expense_formula_data.name                                                  as "Data Name",
    port_expense_formula_data.value                                                 as "Data Value"
    -- port_expense_formula_data.value::jsonb->>'from'                                 as "From", 
    -- port_expense_formula_data.value::jsonb->>'to'                                   as "To",
    -- port_expense_formula_data.value::jsonb->>'unmoor'                               as "Unmoor",
    -- port_expense_formula_data.value::jsonb->>'var'                                  as "Var",
    -- port_expense_formula_data.value::jsonb->>'tariff'                               as "Tariff",
    -- port_expense_formula_data.value::jsonb->>'rate'                                 as "Rate",
    -- port_expense_formula_data.value::jsonb->>'lumpsum'                              as "Lumpsum",
    -- port_expense_formula_data.value::jsonb->>'max'                                  as "Max",
    -- port_expense_formula_data.value::jsonb->>'min'                                  as "Min",
    -- port_expense_formula_data.value::jsonb->>'charge'                               as "Charge",
    -- port_expense_formula_data.value::jsonb->>'day'                                  as "Day",
    -- port_expense_formula_data.value::jsonb->>'night'                                as "Night"

and a sample of the output:

enter image description here

Tags:

3

Answers


  1. 0

    First flatten the table using jsonb_array_elements to unnest the JSON array and a lateral join in order to produce a row per array element.

    SELECT
 port.id                         as "Port ID",
 port.name                       as "Port Name",
 country.name                    as "Country",
 port_expense.id                 as "Port Expense ID",
 port_expense.name::text         as "Port Expense",
 terminal.id                     as "Terminal ID",
 terminal.name                   as "Terminal Name",
 berth.id                        as "Berth ID",
 berth.name                      as "Berth Name",
 port_expense_formula_data.name  as "Data Name",
 port_expense_formula_data.value as "Data Value"
  j->>'from'    as "From", 
  j->>'to'      as "To",
  j->>'unmoor'  as "Unmoor",
  j->>'var'     as "Var",
  j->>'tariff'  as "Tariff",
  j->>'rate'    as "Rate",
  j->>'lumpsum' as "Lumpsum",
  j->>'max'     as "Max",
  j->>'min'     as "Min",
  j->>'charge'  as "Charge",
  j->>'day'     as "Day",
  j->>'night'   as "Night" 
from <your joined tables here> 
cross join lateral 
  jsonb_array_elements(port_expense_formula_data.value::jsonb) j;

    cross join lateral is the verbose syntax. It can be replaced by , lateral or – in the case of a set-returning function as above – just a comma.

    You may simulate/experiment with this example:

    with t(a, b, jdata) as 
(
 values
 (1, 2, '[{"x":10, "y":20}, {"x":110, "y":120}]'),
 (3, 4, '[{"x":11, "y":21}, {"x":111, "y":121, "z":221}]'),
 (5, 6, '[{"x":12, "y":22}, {"x":112, "y":122, "z":222}]'),
 (7, 8, '[{"x":13, "y":23}, {"x":113, "y":123}]')
)
select t.a, t.b, 
       j->>'x' as x, j->>'y' as y, j->>'z' as z
from t, jsonb_array_elements(t.jdata::jsonb) j;
    Login or Signup to reply.
  2. 0

    First, you need to differentiate whether jsonb column contains a single json or array of jsons. Therefore, I’d do something like:

    WITH array_json as (
select * from port_expense_formula_data 
where jsonb_typeof(port_expense_formula_data.value::jsonb) = 'array'
), object_json as (
select * from port_expense_formula_data 
where jsonb_typeof(port_expense_formula_data.value::jsonb) = 'object'
)
SELECT
 port.id                         as "Port ID",
 port.name                       as "Port Name",
 country.name                    as "Country",
 port_expense.id                 as "Port Expense ID",
 port_expense.name::text         as "Port Expense",
 terminal.id                     as "Terminal ID",
 terminal.name                   as "Terminal Name",
 berth.id                        as "Berth ID",
 berth.name                      as "Berth Name",
 array_json.name  as "Data Name",
 array_json.value as "Data Value"
  j->>'from'    as "From", 
  j->>'to'      as "To",
  j->>'unmoor'  as "Unmoor",
  j->>'var'     as "Var",
  j->>'tariff'  as "Tariff",
  j->>'rate'    as "Rate",
  j->>'lumpsum' as "Lumpsum",
  j->>'max'     as "Max",
  j->>'min'     as "Min",
  j->>'charge'  as "Charge",
  j->>'day'     as "Day",
  j->>'night'   as "Night" 
from array_json, lateral 
  jsonb_array_elements(array_json.value::jsonb) j
UNION ALL
SELECT
 port.id                         as "Port ID",
 port.name                       as "Port Name",
 country.name                    as "Country",
 port_expense.id                 as "Port Expense ID",
 port_expense.name::text         as "Port Expense",
 terminal.id                     as "Terminal ID",
 terminal.name                   as "Terminal Name",
 berth.id                        as "Berth ID",
 berth.name                      as "Berth Name",
 object_json.name  as "Data Name",
 object_json.value as "Data Value"
 object_json->>'from'    as "From", 
 object_json->>'to'      as "To",
 object_json->>'unmoor'  as "Unmoor",
 object_json->>'var'     as "Var",
 object_json->>'tariff'  as "Tariff",
 object_json->>'rate'    as "Rate",
 object_json->>'lumpsum' as "Lumpsum",
 object_json->>'max'     as "Max",
 object_json->>'min'     as "Min",
 object_json->>'charge'  as "Charge",
 object_json->>'day'     as "Day",
 object_json->>'night'   as "Night" 
from object_json
;
    Login or Signup to reply.
  3. 0

    You’re looking for the jsonb_array_elements table function to work with arrays. However, your data is extra challenging because you are storing a mix of objects and arrays-of-objects, try to avoid that if possible. So I’d first wrap the objects in an array using jsonb_typeof and jsonb_build_array:

    SELECT
  *,
  jsonb_array_elements(CASE jsonb_typeof(pefd.value)
    WHEN 'array' THEN pefd.value
    WHEN 'object' THEN jsonb_build_array(pefd.value)
  --ELSE NULL
  END) AS obj
FROM
  port_expense_formula_data pefd;

    or to access the obj properties:

    SELECT
  *,
  (obj ->> 'rate')::float AS "rate",
 obj ->> 'from' AS "from",
 obj ->> 'to' AS "to",
 (obj ->> 'fixed')::int AS "fixed"
FROM
  port_expense_formula_data pefd,
  jsonb_array_elements(CASE jsonb_typeof(pefd.value)
    WHEN 'array' THEN pefd.value
    WHEN 'object' THEN jsonb_build_array(pefd.value)
  --ELSE NULL
  END) AS obj;

    However, to extract JSON properties into columns, I recommend to use records via jsonb_to_recordset instead of jsonb_array_elements, which also lets you easily choose the column type:

    SELECT *
FROM
  port_expense_formula_data pefd,
  jsonb_to_recordset(CASE jsonb_typeof(pefd.value)e)
    WHEN 'array' THEN pefd.value
    WHEN 'object' THEN jsonb_build_array(pefd.value)
  --ELSE NULL
  END) AS obj(
    "rate" float,
    "from" text,
    "to" text,
    "fixed" int
  );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search