Inserting array of jsonb objects into postgresql table column

Question

I have a table with two columns application and projects. While application is a simple column of varchar type, projects should hold array of jsonb objects.

A sample record is:

application | projects
-----------------------
App1        | [{"project": "project1", "url":"url1"},{"project": "project2", "url": "url2"}]

Now there is a json file which holds the data to be inserted into this table programmatically in the exact same structure as this table which needs to be read through Node.js and inserted into the table.

My question is how do I write an insert query which inserts the projects field correctly into the table. I am a beginner in Postgresql and unaware of the function that can do this. I want to write something like this:

Insert into <tablename> values('App1', 
some_function_that_converts_string_to_jsonb_array('[{"project": "project1", "url":"url1"},
{"project": "project2", "url": "url2"}]');

What function of Postgresql can achieve this? I have tried using jsonb_build_array but it is not working.

Thanks!

EDIT:

The value of the projects column should be that I can query on it.

It should work with just the string representation of the JSON: . . . values ('App1', '[1, 2, 3]') — Mike Organek
– Mike Organek, Commented Jul 26, 2023 at 12:36
I tried it. It is throwing error. "[" must introduce explicitly-specified array dimensions.. The type of projects column is jsonb[] — NodeFriend
– NodeFriend, Commented Jul 26, 2023 at 12:40
Querying can be done, but the syntax will be verbose. Inserting a new project will be problematic. If you are in the planning stages, then you should use a normalized schema to simplify those expected future operations. This is the classic case of "pay me now or pay me later." Taking the json shortcut with the one-time, up-front data modeling means you will pay dearly on every query, insert, or update you write in the future. — Mike Organek
– Mike Organek, Commented Jul 26, 2023 at 12:45
I still say to use a normalized schema, but to get past your error about "explicitly-specified array dimensions', change the type of the projects column to jsonb. Please see: stackoverflow.com/a/62917486/13808319 — Mike Organek
– Mike Organek, Commented Jul 26, 2023 at 12:46

Silvan · Accepted Answer · 2023-07-26 13:09:52Z

0

If I understand you correct you want to insert a JSON as a string into a postgres table column of the type jsonb?

Then this is your query:

INSERT INTO <YOUR_TABLE>(projects) VALUES ('[{"project": "project1", "url":"url1"},{"project": "project2", "url": "url2"}]')

You can query the results using the methods described here: https://www.postgresql.org/docs/9.4/functions-json.html

You can add the JSON as an array object with this query:

INSERT INTO <YOUR TABLE>(projects) VALUES (array['[{"project": "project1", "url":"url1"},{"project": "project2", "url": "url2"}]'::JSONB])

edited Jul 26, 2023 at 13:09

answered Jul 26, 2023 at 12:52

Silvan

4553 silver badges15 bronze badges

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

6 Comments

NodeFriend Over a year ago

No, the type of column is jsonb[ ]. The column should hold array of jsonb objects

Silvan Over a year ago

sorry did not ready properly. I added the insert array as second query. But I can tell you working with json array objects is gonna be pretty painful...

NodeFriend Over a year ago

What should be the better approach then. I am a very novice to Postgresql

Silvan Over a year ago

You should normalize your table properly. Take the effort and unpackage your json in javascript and insert all values in proper tables and columns. e.g make a project table with an id, name, url etc... Especially if you want to query the data in the json often. The JSON approach seems fast at first but it's gonna get pretty annoying because u have to write complex querys for the simplest things.

NodeFriend Over a year ago

So you want to say that I make another project table having application as foreign key and then perform a join query on both tables?

|

Collectives™ on Stack Overflow

Inserting array of jsonb objects into postgresql table column

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related