How to simplify the JSON returned by a query using PostgreSQL 15

Question

I am stuck with the PostgreSQL json_build_object function. I am trying to build a query to return a JSON results like this:

{
  "id": 1001,
  "is_active": true,
  "created_dt": "2023-04-22T14:37:37.985197",
  "updated_dt": "2023-04-22T14:37:37.985197",
  "author_id": 1004,
  "article_type": "TUTORIAL",
  "lang_code": "EN",
  "editorial_state": "DRAFT",
  "title": "Programming is fun!",
  "subtitle": "In this case, it is the third revision.",
  "published_dt": null,
  "is_eol": false,
  "is_featured": false,
  "is_front_page": false,
  "is_new": false,
  "is_onsale": false,
  "is_own": false,
  "is_top10": false,
  "is_top100": true,
  "bookmark_count": 3,
  "clap_count": 43,
  "reading_count": 14,
  "share_count": 2,
  "view_count": 498,
  "rel_articles": [
    {
      "id": 1001,
      "article_type": "TUTORIAL",
      "lang_code": "EN",
      "title": "Programming is fun!",
      "reading_time": 0.00,
      "word_count": 0,
      "subtitle": "In this case, it is the third revision."
    },
    {
      "id": 1018,
      "article_type": "ARTICLE",
      "lang_code": "EN",
      "title": "ARTICLE-1018-EN",
      "reading_time": 0.00,
      "word_count": 0,
      "subtitle": null
    },
    {
      "id": 1023,
      "article_type": "TUTORIAL",
      "lang_code": "EN",
      "title": "ARTICLE-1023-EN",
      "reading_time": 0.00,
      "word_count": 0,
      "subtitle": null
    }
  ]
}

However, some extra naming article and json_agg are being added to the returned string:

{
  "article": {
    "id": 1001,
    "is_active": true,
    "created_dt": "2023-04-22T14:37:37.985197",
    "updated_dt": "2023-04-22T14:37:37.985197",
    "author_id": 1004,
    "article_type": "TUTORIAL",
    "lang_code": "EN",
    "editorial_state": "DRAFT",
    "title": "Programming is fun!",
    "subtitle": "In this case, it is the third revision.",
    "published_dt": null,
    "is_eol": false,
    "is_featured": false,
    "is_front_page": false,
    "is_new": false,
    "is_onsale": false,
    "is_own": false,
    "is_top10": false,
    "is_top100": true,
    "bookmark_count": 3,
    "clap_count": 43,
    "reading_count": 14,
    "share_count": 2,
    "view_count": 498
  },
  "rel_articles": {
    "json_agg": [
      {
        "id": 1001,
        "article_type": "TUTORIAL",
        "lang_code": "EN",
        "title": "Programming is fun!",
        "reading_time": 0.00,
        "word_count": 0,
        "subtitle": "In this case, it is the third revision."
      },
      {
        "id": 1018,
        "article_type": "ARTICLE",
        "lang_code": "EN",
        "title": "ARTICLE-1018-EN",
        "reading_time": 0.00,
        "word_count": 0,
        "subtitle": null
      },
      {
        "id": 1023,
        "article_type": "TUTORIAL",
        "lang_code": "EN",
        "title": "ARTICLE-1023-EN",
        "reading_time": 0.00,
        "word_count": 0,
        "subtitle": null
      }
    ]
  }
}

The query is this:

with json_article as (
  select
    tj.*
  from (
    select
      ta.*,
      /* language */
            tx.lang_code,
      tx.editorial_state,
      tx.title,
      tx.subtitle,
      tx.published_dt,
      /* facts */
      tf.is_eol,
      tf.is_featured,
      tf.is_front_page,
      tf.is_new,
      tf.is_onsale,
      tf.is_own,
      tf.is_top10,
      tf.is_top100,
      tf.bookmark_count,
      tf.clap_count,
      tf.reading_count,
      tf.share_count,
      tf.view_count        
    from 
      cnt_article ta
    left join
      cnt_doc_title tx on tx.doc_id = ta.id and tx.doc_type = 'ARTICLE' and  
      tx.lang_code = 'EN'
    left join
      cnt_doc_fact tf on tf.doc_id = ta.id and tf.doc_type = 'ARTICLE'
    where 
      ta.id = 1001
  ) as tj
), json_rel_articles as (
    select
      json_agg(row_to_json(trel))
    from (
      select
        tr.doc2_id as id,
        ta.article_type,
        tx.lang_code,
        tx.title,
        tx.reading_time,
        tx.word_count,
        tx.subtitle
      from 
        cnt_doc_relation tr
      left join
        cnt_doc_title tx on tx.doc_id = tr.doc2_id and tx.doc_type = tr.doc2_type and 
        tx.lang_code = 'EN'
      left join
        cnt_article ta on ta.id = tr.doc2_id
      where 
        tr.doc1_type = 'ARTICLE' and tr.doc1_id = 1001 and tr.relation_type = 'REF' and 
        tr.doc2_type = 'ARTICLE'
    ) as trel
)
select
  json_build_object(
    'article',
    json_article,
    'rel_articles',
    json_rel_articles
    )
from
  json_article, json_rel_articles;

The "article" braces are my fault, as json_build_object requires keys and values. The "json_agg" is something I don't understand. I suppose I have to avoid using json_build_object, but I am clueless of what to use instead.

Any idea of how to modify the query to produce the required output?

Thanks in advance.

1. Do not mix subqueries and CTEs. 2. Use jsonb instead of json. 3. Instead of `json_build_object()` or `jsonb_build_object(), use `to_jsonb()`. 4. The `json_agg` key appears in your result because you did not alias the column. — Mike Organek, Apr 23 '23 at 01:28

score 0 · Answer 1 · answered Apr 22 '23 at 14:00

Suppose you have the following tables:

CREATE TABLE head(i INTEGER);
INSERT INTO head VALUES   (1),   (2),   (3);

CREATE TABLE details(i INTEGER, c CHAR(1));
INSERT INTO details VALUES
   (1,'a'),   (1,'b'),   (1,'c'),
   (2,'d'),   (2,'e'),   (2,'f'),
   (3,'g'),   (3,'h'),   (3,'i');

A Simplified example, with these simple tables:

SELECT 
   JSON_BUILD_OBJECT('head', i, 
                     'details',(SELECT JSON_AGG(c) 
                                FROM details 
                                WHERE details.i = head.i)
                    )
FROM head
WHERE i=2;

returns:

{"head" : 2, "details" : ["d", "e", "f"]}

The explanation is in the docs... (somewhere).

see: DBFIDDLE

This is not equivalent to the OP. Only the column `c` is being aggregated to the JSON array. JSON objects as `{"i":1, "c":"a"}` should appear. — coterobarros, Apr 22 '23 at 16:58

How to simplify the JSON returned by a query using PostgreSQL 15

1 Answers1