how to insert JSON array of objects into PostgreSQL as separate elements

Question

I have JSON and I'm sending it to postgresql function. I need to insert every object inside weekDays array in a separate row and every key inside the object in a separate column and it needs to include weekId.

My table looks like this:

{
   "weekId":20,
   "weekDays":[
      {
         "day_of_week":"Monday",
         "working_hours":22.5,
         "description":"text",
         "date":"May 22, 2019"
      },
      {
         "day_of_week":"Tuesday",
         "working_hours":22.5,
         "description":"text",
         "date":"May 22, 2019"
      }
   ]
}

So what is the best way to do this? I can do something like this:

INSERT INTO timesheet(day_of_week, working_hours, description, date)
SELECT day_of_week, working_hours, description, date
FROM json_to_recordset(weeks_days)
AS x(day_of_week varchar, working_hours REAL, description text, date timestamp);

But how to include weekId that is not in array?

klin · Accepted Answer · 2019-05-30 08:48:41Z

1

Use ->> operator to get weekId as text and cast it to integer, e.g.:

WITH my_data(jsonb_column) AS (
VALUES
('{
   "weekId":20,
   "weekDays":[
      {
         "day_of_week":"Monday",
         "working_hours":22.5,
         "description":"text",
         "date":"May 22, 2019"
      },
      {
         "day_of_week":"Tuesday",
         "working_hours":22.5,
         "description":"text",
         "date":"May 22, 2019"
      }
   ]
}'::jsonb)
)

INSERT INTO timesheet(weekid, day_of_week, working_hours, description, date)
SELECT (jsonb_column->>'weekId')::int, day_of_week, working_hours, description, date
FROM my_data
CROSS JOIN jsonb_to_recordset(jsonb_column->'weekDays')
AS x(day_of_week varchar, working_hours REAL, description text, date timestamp)
RETURNING *;

 id | weekid | day_of_week | working_hours | description |        date         
----+--------+-------------+---------------+-------------+---------------------
  1 |     20 | Monday      |          22.5 | text        | 2019-05-22 00:00:00
  2 |     20 | Tuesday     |          22.5 | text        | 2019-05-22 00:00:00
(2 rows)

In Postgres 11 you can cast jsonb number to integer (-> instead of ->>):

SELECT (jsonb_column->'weekId')::int, day_of_week, working_hours, description, date

answered May 30, 2019 at 8:48

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Edin Puzic Over a year ago

It works! just one question... WITH my_data(jsonb_column) AS ( VALUES() ) Creates something like temp table?

klin Over a year ago

Yes, in some sense. It's a handy way to prepare input data in the form of a table. Read more in the documentation.

Collectives™ on Stack Overflow

how to insert JSON array of objects into PostgreSQL as separate elements

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related