0

I have a table in SQL containing a column in JSON format.

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?

Improve this question
5
  • 1
    Your query will need to generate a fixed set of columns, for all rows. The columns only depend on the query (and database schema), never on the data. Commented Jan 9, 2024 at 11:33
  • How do you want to handle the arrays where you have multiple data values per row? Commented Jan 9, 2024 at 11:34
  • Yes i would need like 10s of new columns probably based on the data included in the JSON. Each row that does not have the specific object will show null. In regards to the second comment I would want the specific row to be multiplied by the number of data values for each array so if an array has 2 values just duplicate the row, if it has more create a new row for each value. Does that make sense? Commented Jan 9, 2024 at 11:37
  • 1
    No, you need to decide beforehand which columns you query, it cannot be dynamic depending on the data. If you really need that, fetch the JSON value and do post-processing in the application code. Commented Jan 9, 2024 at 11:41
  • I edited my post. this is somehow the required output. But arrays do not work. Commented Jan 9, 2024 at 11:45

3 Answers 3

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I missed that some of the JSON values are arrays and other are not. @Bergi has got it the right way.
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
  );
Improve this answer

1 Comment

Thank you very much for all the effort @Bergi! I will give it a shoot tomorrow and let you know if I got a result or not!
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
;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.