Postgres creating a JSON object for each query

Question

Forgive me if I'm approaching / explaining this in the wrong manner.

I have a table which is filled with information about articles we post on our site.

What I would like to do is create a JSON object which categorises by the 'sub_speciality_id' and shows the 3 most recent articles for each.

so essentially it looks like this

for each article we have an id, news_type_id, title and sub_speciality_id

So essentially I want to SELECT the different sub_speciality_ids which I can do with

/* selecting the sub_speciality ids where an article exists */
select DISTINCT(sub_speciality_id) from news_item where news_type_id = 1 AND sub_speciality_id is not null

Then for each of the sub_specialities I want to use that to produce a JSON object much like

SELECT json_agg(row_to_json(r))
FROM
(select * from news_item where news_type_id = 1 AND sub_speciality_id =replace_me ORDER BY create_dt DESC LIMIT 3)r

BUT replacing 'replace_me' with the ID above for each

So I guess it would look something like (although I'm sure I've made some errors in formatting that):

{

    "sub_specialities": {
        "1": [
                    {
                    "id": 2328,
                    "news_type_id": 1,
                    "title": "This is a title",
                    "sub_speciality_id": 1
                    },{
                    "id": 2287,
                    "news_type_id": 1,
                    "title": "Blood Conservation Techniques",
                    "sub_speciality_id": 1
                    },{
                    "id": 2278,
                    "news_type_id": 1,
                    "title": "A Great Way to Do Apneic Oxygenation - Buccal O2",
                    "sub_speciality_id": 1
                    }],
        "2": [
                    {
                    "id": 2328,
                    "news_type_id": 1,
                    "title": "This is a title",
                    "sub_speciality_id": 2
                    },{
                    "id": 2287,
                    "news_type_id": 1,
                    "title": "Blood Conservation Techniques",
                    "sub_speciality_id": 2
                    },{
                    "id": 2278,
                    "news_type_id": 1,
                    "title": "A Great Way to Do Apneic Oxygenation - Buccal O2",
                    "sub_speciality_id": 2
                    }],
        "3": [
                    {
                    "id": 2328,
                    "news_type_id": 1,
                    "title": "This is a title",
                    "sub_speciality_id": 3
                    },{
                    "id": 2287,
                    "news_type_id": 1,
                    "title": "Blood Conservation Techniques",
                    "sub_speciality_id": 3
                    },{
                    "id": 2278,
                    "news_type_id": 1,
                    "title": "A Great Way to Do Apneic Oxygenation - Buccal O2",
                    "sub_speciality_id": 3
                    }]

    }
  }

404 · Accepted Answer · 2018-12-03 17:04:48Z

1

Dataset:

CREATE TABLE t (sub_speciality_id INTEGER, news_type_id INTEGER, title TEXT, content TEXT);

INSERT INTO t
VALUES
  (1, 1, 'a', 'some text a'),
  (1, 2, 'b', 'some text b'),
  (2, 1, 'c', 'some text c'),
  (1, 1, 'd', 'some text d'),
  (2, 2, 'e', 'some text e'),
  (2, 1, 'f', 'some text f');

Query:

SELECT JSON_BUILD_OBJECT('sub_specialities', JSON_OBJECT_AGG(sub_speciality_id, item))
FROM (
  SELECT sub_speciality_id, JSON_AGG(ROW_TO_JSON(t)) AS item
  FROM t
  WHERE news_type_id = 1
  GROUP BY sub_speciality_id
) AS j

Returns (after prettifying it):

{
    "sub_specialities": {
        "1": [{
            "sub_speciality_id": 1,
            "news_type_id": 1,
            "title": "a",
            "content": "some text a"
        }, {
            "sub_speciality_id": 1,
            "news_type_id": 1,
            "title": "d",
            "content": "some text d"
        }],
        "2": [{
            "sub_speciality_id": 2,
            "news_type_id": 1,
            "title": "c",
            "content": "some text c"
        }, {
            "sub_speciality_id": 2,
            "news_type_id": 1,
            "title": "f",
            "content": "some text f"
        }]
    }
}

answered Dec 3, 2018 at 17:04

404

8,7522 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Charlie Over a year ago

I tried replacing 't' with 'news_item' but I get ERROR: column "t" does not exist LINE 3: SELECT sub_speciality_id, JSON_AGG(ROW_TO_JSON(t)) AS item If I replace the other 't' as well I get ERROR: field name must not be null SQL state: 22023

404 Over a year ago

@Charlie You probably have some records where sub_speciality_id is NULL. So you need to decide how to handle that, because you are using that value as a field name. If you want to exclude them, change the WHERE to WHERE news_type_id = 1 AND sub_speciality_id IS NOT NULL, otherwise COALESCE it, e.g. SELECT COALESCE(sub_speciality_id, 0) AS sub_speciality_id, JSON_AGG(...) ... GROUP BY COALESCE(sub_speciality_id, 0)

Charlie Over a year ago

amazing thank you. One other question - how do I order by and limit so for each sub_speciality there is only 3 results?

404 Over a year ago

@Charlie It needs a couple more subqueries to do that; first to get a row number per sub_speciality_id, in order to get only 3 items per sub_speciality_id; then another subquery to get a row without the row_number, because otherwise that value will be in the json object. Anyway you can take a look at it here: db-fiddle.com/f/pgqTkqHBGFfgNLFAdLDGqr/0 I've done the ordering for the LIMIT by title (in the ROW_NUMBER bit), but of course you would put whatever field you want to order by when deciding which 3 to get.

Collectives™ on Stack Overflow

Postgres creating a JSON object for each query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related