ERROR : cannot call jsonb_to_recordset on a non-array (postgresql)

Question

in column steps i have json values like [{"id":"ali","status":"open","reminder":"tomorrow","show_due_date":"true"}]

and i want to query new table like separate column for each key

id | status| reminder | show_due_date

i wrote this script but getting error of cannot call jsonb_to_recordset on a non-array

WITH series (jsonbrecords) AS (Select steps::jsonb from files)
INSERT INTO new
    (column1,
     column2,
     column3,
     column4)
SELECT  t."id", t."status", t."reminder", t."show_due_date"
  FROM series
 CROSS JOIN LATERAL
 jsonb_array_elements(jsonbrecords) AS x(doc),
 jsonb_to_recordset(x.doc) as t("id" text, "status" text,"reminder" text,"show_due_date" text)

MySQL or PostgreSQL? I assume you’re not working with both at the same time with the same query — Sami Kuhmonen
– Sami Kuhmonen, Commented Jun 16, 2020 at 10:24

Barbaros Özhan · Accepted Answer · 2020-06-16 10:56:36Z

3

You can bring each respective columns using the below query without need of the other function :

SELECT x.doc ->> 'id' AS id, 
       x.doc ->> 'status' AS status,
       x.doc ->> 'reminder' AS reminder, 
       x.doc ->> 'show_due_date' AS show_due_date
  FROM series
 CROSS JOIN LATERAL jsonb_array_elements(jsonbrecords) AS x(doc)

Demo

You can go on with this query to insert into a new table if you wish :

INSERT INTO new_table(column1,column2,column3,column4)
<the above query>

edited Jun 16, 2020 at 10:56

answered Jun 16, 2020 at 10:50

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

7 Comments

muhammad ahmad falak Over a year ago

thnx for that, its really helpful .but again i have one question if i'm getting my json values from another table. should i have to do the same like that? with series (jsonbrecords) as (Select steps::jsonb from court_files_procedureblueprint) INSERT INTO ahmad VALUES(series.steps::jsonb);

Barbaros Özhan Over a year ago

hi @muhammadahmadfalak , you're welcome. Can you add some clarifications, what you exactly want, on the demo which I shared in the answer, please.

muhammad ahmad falak Over a year ago

actually in the demo you are giving values manually ,but i want that from another table which is files.steps . so i tried to do INSERT INTO series VALUES("files.steps"::jsonb); but not working for me

Barbaros Özhan Over a year ago

well, can you share what have you tried in the demo @muhammadahmadfalak ?

muhammad ahmad falak Over a year ago

dbfiddle.uk/… and here files.steps are with json values

|

Bergi · Accepted Answer · 2020-06-16 11:07:54Z

2

You're unnesting the json array twice, once with jsonb_array_elements and once with jsonb_to_recordset. You need only one of them, e.g.

INSERT INTO new(column1, column2, column3, column4)
SELECT t."id", t."status", t."reminder", t."show_due_date"
  FROM files f
  CROSS JOIN LATERAL jsonb_to_recordset(f.steps::jsonb) AS t("id" text, "status" text, "reminder" text, "show_due_date" text)

answered Jun 16, 2020 at 11:07

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

9 Comments

muhammad ahmad falak Over a year ago

thnx for help but it's still giving the same error with this code "ERROR: cannot call jsonb_to_recordset on a non-array"

Bergi Over a year ago

@muhammadahmadfalak Works fine for me. Can you share your exact table definition and data sample?

muhammad ahmad falak Over a year ago

yeah its work here, it should have to work for me too. i will look in to that more. thnx for help :) PS : sorry but cannot share exact table

Bergi Over a year ago

@muhammadahmadfalak Ok, you don't need to share the exact definition, just a complete minimal reproducible example that demonstrates the error if you need further help with it.

muhammad ahmad falak Over a year ago

acatually files are with six columns and all of the data is getting from django . And steps column is only with the json fields. and yes steps are not uniform means in some rows it have a 2 json values and in some it have 5 or more. but i also research alot and i think i have problem in database section and in retrieving from database it give me error of non-array

|

Collectives™ on Stack Overflow

ERROR : cannot call jsonb_to_recordset on a non-array (postgresql)

2 Answers 2

7 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related