PostgreSQL converting multiple rows to json array in json_build_object

Question

There are two tables in relation one-to-many:

CREATE TABLE user (
  id bigserial PRIMARY KEY,
  name varchar(255) NOT NULL UNIQUE,
)

CREATE TABLE user_pets (
  id bigserial PRIMARY KEY,
  user_id int8 FOREIGN KEY REFERENCES user(id),
  name varchar(255)
)

user.id is generated by DEFAULT.

Another table containing json of users and their pets:

CREATE TABLE user_json (
  user_id int8 FOREIGN KEY REFERENCES user(id),
  full_info json
)

I want my json look like this:

{
  "id": 1,
  "name": "John",
  "pets": [
    {
      "id": 1,
      "user": 1,
      "name": "Kitty"
    },
    {
      "id": 2,
      "user": 1,
      "name": "Cat"
    }
  ]
}

I'm using json_build_object to process user, but I don't know how to convert multiple rows to array of pets:

json_build_object(
  'id', user.id,
  'name', user.name,
  'pets', --WHAT SHALL I PASS HERE?
)

Postgres 10

Answer 1

In the end, this worked for me:

WITH all_pets AS (
  SELECT *
  FROM pets
  WHERE pets.user_id = 1
)
INSERT INTO user_json (full_info)
SELECT
json_build_object(
  'id', user.id,
  'name', user.name,
  'pets', (
    SELECT json_agg(all_pets)
    FROM pets
  )
)
FROM user
WHERE user.id = 1;

Answer 2

You can use json_to_recordset(), and apply it to your json object using a CROSS JOIN LATERAL:

SELECT a.full_info->>'id', a.full_info->>'name', b.*
FROM user_json a
CROSS JOIN lateral json_to_recordset(a.full_info->'pets')
as b("id" integer
      , "user" integer
      , "name" varchar)