Generating JSON in PostgreSQL

Question

I have a column named as userId

|userId|
|  1   |
|  2   |

when I select this column I want to get value in form of json like this:-

user:{
  id:1
}
user:{
  id:2
}

is there any json function to do this in PostgreSQL?

Jim Jones · Accepted Answer · 2021-07-02 07:05:30Z

1

Use json_build_object:

WITH t (userid) AS (
  VALUES (1),(2)
)
SELECT json_build_object('user',json_build_object('id',userid)) 
FROM t;
   json_build_object   
-----------------------
 {"user" : {"id" : 1}}
 {"user" : {"id" : 2}}
(2 rows)

edited Jul 2, 2021 at 7:05

answered Jul 2, 2021 at 6:59

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

2 Comments

a_dog_with_no_master Over a year ago

{ "user" : 1, "user" : 1} this is the result.. not exactly what I'm looking for

Jim Jones Over a year ago

@a_dog_with_no_master nested json_build_object might do the trick.. check the edit :)

Collectives™ on Stack Overflow

Generating JSON in PostgreSQL

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