How to get a table name inside a pg query with json?

user_19763924 0
Viewed 37

I'm trying to get a json with a table name inside with a row, I've trying:

Create a table:

create table if not exists customer (
    _id serial,
    first_name varchar(50),
    constraint pk_customer primary key (_id)
);

Insert some data:

insert into customer(first_name) values ('Customer1');

Then:

SELECT json_agg(customer) as customer FROM customer;

Result:

[{"_id":1,"first_name":"Customer1"}, 
 {"_id":2,"first_name":"Customer2"}]

Expected Result:

{
   "Customer":[
      {
         "_id":1,
         "first_name":"Customer1"
      },
      {
         "_id":2,
         "first_name":"Customer2"
      }
   ]
}

Someone knows if this is possible directly from database?

nosql postgresql sql json
1 Answers

for a nested json you have to build it with json_build_object

you should also check the other json functions https://www.postgresql.org/docs/14/functions-json.html

SELECT json_build_object('Customer',json_agg(customer)) as customer FROM customer;

| customer                                                                                     |
| :------------------------------------------------------------------------------------------- |
| {"Customer" : [{"_id":1,"first_name":"Customer1"}, <br> {"_id":2,"first_name":"Customer2"}]} |

db<>fiddle here

user_51935360
Related