How to generate JSON array from multiple rows, then return with values of another table

debugcn Published at Dev

Exitialis

I am trying to build a query which combines rows of one table into a JSON array, I then want that array to be part of the return.

I know how to do a simple query like

SELECT *
FROM public.template 
WHERE id=1

And I have worked out how to produce the JSON array that I want

SELECT array_to_json(array_agg(to_json(fields)))
FROM (
    SELECT id, name, format, data
    FROM public.field
    WHERE template_id = 1
) fields

However, I cannot work out how to combine the two, so that the result is a number of fields from public.template with the output of the second query being one of the returned fields.

I am using PostGreSQL 9.6.6

Edit, as requested more information, a definition of field and template tables and a sample of each queries output.

Currently, I have a JSONB row on the template table which I am using to store an array of fields, but I want to move fields to their own table so that I can more easily enforce a schema on them.

Template table contains:

id
name
data
organisation_id

But I would like to remove data and replace it with the field table which contains:

id
name
format
data
template_id

At the moment the output of the first query is:

  {
    "id": 1,
    "name": "Test Template",
    "data": [
      {
        "id": "1",
        "data": null,
        "name": "Assigned User",
        "format": "String"
      },
      {
        "id": "2",
        "data": null,
        "name": "Office",
        "format": "String"
      },
      {
        "id": "3",
        "data": null,
        "name": "Department",
        "format": "String"
      }
    ],
    "id_organisation": 1
  }

This output is what I would like to recreate using one query and both tables. The second query outputs this, but I do not know how to merge it into a single query:

[{
    "id": 1,
    "name": "Assigned User",
    "format": "String",
    "data": null
},{
    "id": 2,
    "name": "Office",
    "format": "String",
    "data": null
},{
    "id": 3,
    "name": "Department",
    "format": "String",
    "data": null
}]

Exitialis

Sorry for poorly phrasing what I was trying to achieve, after hours of Googling I have worked it out and it was a lot more simple than I thought in my ignorance.

SELECT id, name, data
FROM public.template, (
    SELECT array_to_json(array_agg(to_json(fields)))
        FROM (
            SELECT id, name, format, data
            FROM public.field
            WHERE template_id = 1
        ) fields
) as data
WHERE id = 1

I wanted the result of the subquery to be a column in the ouput rather than compiling the entire output table as a JSON.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-08-14

Comments

0 comments

From Dev

How to extract multiple rows from a table based on values from multiple columns from another table and then concatenate in SQL?

From Dev

Related Related

Article