14.2.3 Constructing JSON in SQL

Use JSON_OBJECT and JSON_ARRAYAGG SQL functions to construct JSON from relational data.

For example, the following query retrieves one JSON object per ACTION_ITEMS row. Notice you can include a column as is, with a default property name, or explicitly specify a column's property name using the value keyword. Study the team member name, user_id, and role. For consistency, it's fine to use value even if you choose the same lowercase version of the column name that would have been the default.

select json_object(
          '_id' value ai.id,
          ai.name,
          ai.status,
          'team' value (select json_arrayagg(
                                  json_object(
                                     'team_member_id' value tm.id,
                                     'name'           value s.name,
                                     'user_id'        value tm.user_id,
                                     'role'           value tm.role))
                          from action_item_team_members tm
                          join staff s
                            on tm.user_id = s.id
                         where tm.action_id = ai.id))
  from action_items ai

Each row of the result, looks like the following:

{
  "_id": 14,
  "name": "End of Year Party",
  "status: "OPEN",
  "team": [
    {
      "team_member_id": 23,
      "name": "David",
      "user_id": 4,
      "role": "MEMBER"
    },
    {
      "team_member_id": 24,
      "name": "Georgia",
      "user_id": 5,
      "role": "LEAD"
    },
    {
      "team_member_id": 25,
      "name": "Jane",
      "user_id": 13,
      "role": "MEMBER"
    }
  ]
}

When using JSON_ARRAYAGG to collect one or more elements into an array, you can include an ORDER BY clause to sort the array elements. For example, to sort the team members first by role, then by name, you can write:

select json_object(
          '_id' value ai.id,
          ai.name,
          ai.status,
          'team' value (select json_arrayagg(
                                  json_object(
                                     'team_member_id' value tm.id,
                                     'name'           value s.name,
                                     'user_id'        value tm.user_id,
                                     'role'           value tm.role)
                                  order by tm.role, s.name)
                          from action_item_team_members tm
                          join staff s
                            on tm.user_id = s.id
                         where tm.action_id = ai.id))
  from action_items ai

With this query, the equivalent row in the result shown above looks like this instead:

{
  "_id": 14,
  "name": "End of Year Party",
  "status": "OPEN",
  "team": [
    {
      "team_member_id": 24,
      "name": "Georgia",
      "user_id": 5,
      "role": "LEAD"
    },
    {
      "team_member_id": 23,
      "name": "David",
      "user_id": 4,
      "role": "MEMBER"
    },
    {
      "team_member_id": 25,
      "name": "Jane",
      "user_id": 13,
      "role": "MEMBER"
    }
  ]
}