Concatenate Postgresql jsonb column with row data

Question

I have a table of data with and id column and a jsonb column with object data:

id (int) data (jsonb)

I'm querying the data like this

select row_to_json(t)
from (
    select id, data from accounts
) t;

which gets me data that looks like this:

{
   "id":3,
   "data":
   {
      "emailAddress": "[email protected]", 
      "mobileNumbers": ["5559991212"]
   }
}

I would like to merge the data field into the main record set, I basically want the keys in the data node into the main record:

{
   "id":3,
   "emailAddress": "[email protected]", 
   "mobileNumbers": ["5559991212"]
}

Laurenz Albe · Accepted Answer · 2020-10-07 18:56:59Z

1

You can use

SELECT jsonb_set(data, '{id}', to_jsonb(id))
FROM accounts;

I cannot help remarking that a table with just a primary key and a jsomb column seems like a problematic database design to me.

edited Oct 7, 2020 at 18:56

answered Oct 7, 2020 at 16:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

5 Comments

Gavin Over a year ago

Thanks for the feedback, I've significantly simplified the table design by omitting other columns. I'm taking an approach that stores objects in jsonb, but denormalizing the data into columns for searching and joining to other tables of objects.

Laurenz Albe Over a year ago

Ah, that sounds better.

Gavin Over a year ago

This is the correct answer and I've marked it as such. Can I ask for a bit more advice, that builds off of the first question?

Gavin Over a year ago

I have two related tables to the accounts table that are 1..n, I want to grab that data and nest it inside the data from the account table. Having trouble, I can get the other arrays of data to be adjacent, but not nested:

SELECT jsonb_set(data, '{id}', to_jsonb(id)) account,  ( 	SELECT array_to_json(array_agg(jsonb_set(data, '{id}', to_jsonb(id)))) 	FROM people 	WHERE account_id = accounts.id ) people FROM accounts;

Laurenz Albe Over a year ago

Sounds like a second question.

Collectives™ on Stack Overflow

Concatenate Postgresql jsonb column with row data

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related