How to get an array subquery using postgress sql?

Question

So, what I need is a following json.

var plan = [{
  id: 11,
  title: 'give a title',
  actions: [
   {id: 1,
   planId: 11,
   title: 'give action name'},
   {
     id: 3,
     planId: 11,
     title: 'give another action name'
   }
  ]},
{
      id: 13,
      title: 'thirteen a title',
      actions: [
       {id: 1,
       planId: 13,
       title: 'thirteen action name'},
       {
         id: 3,
         planId: 13,
         title: 'thirteen another action name'
       }
      ]}
]

SO I have 2 tables, plan and actions. The relation between two table is Plan has many actions. Plan(id, titile) Action(id, titile, planId)

SELECT
*,
ARRAY (
    SELECT
        jsonb_build_object ('id',
            m.id,
            'title',
            m.title)
    FROM
        actions a
        INNER JOIN plan p ON p.id = a.planid
    ) AS actions
FROM
    plan

I'm not sure how can I get the related actions under each plan.

Jim Jones · Accepted Answer · 2020-01-06 17:14:24Z

2

These queries might be what you're looking for:

Data sample

CREATE TEMPORARY TABLE plan (id INT, title TEXT);
CREATE TEMPORARY TABLE action (id INT, title TEXT, planid INT);

INSERT INTO plan VALUES (1,'plan a'),(2,'plan b');
INSERT INTO action VALUES (1,'1st action plan a',1),
                          (2,'2nd action plan a',1),
                          (3,'1st action plan b',2);

Query - multiple json records

SELECT 
  json_build_object(
    'id',p.id,'title',p.title,
    'actions',(SELECT json_agg(row_to_json(t)) 
               FROM (SELECT id,title,planid 
               FROM action WHERE planid = p.id) t)) AS myjson
FROM plan p;
                                                                        myjson                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------
 {"id" : 1, "title" : "plan a", "actions" : [{"id":1,"title":"1st action plan a","planid":1}, {"id":2,"title":"2nd action plan a","planid":1}]}
 {"id" : 2, "title" : "plan b", "actions" : [{"id":3,"title":"1st action plan b","planid":2}]}
(2 Zeilen)

Query - single json record

SELECT json_agg(row_to_json(myjson)) FROM 
(SELECT 
  json_build_object(
    'id',p.id,'title',p.title,
    'actions',(SELECT json_agg(row_to_json(t)) 
               FROM (SELECT id,title,planid 
               FROM action WHERE planid = p.id) t)) as plan
FROM plan p) myjson;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"plan":{"id" : 1, "title" : "plan a", "actions" : [{"id":1,"title":"1st action plan a","planid":1}, {"id":2,"title":"2nd action plan a","planid":1}]}}, {"plan":{"id" : 2, "title" : "plan b", "actions" : [{"id":3,"title":"1st action plan b","planid":2}]}}]
(1 Zeile)

edited Jan 6, 2020 at 17:14

answered Jan 6, 2020 at 17:03

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

2 Comments

Abelisto Over a year ago

IMO array_to_json(array_agg(...)) could be simplified just to json_agg(...)

Jim Jones Over a year ago

@Abelisto you're damn right.. just edited my answer. Thanks!

juan_carlos_yl · Accepted Answer · 2022-09-28 21:38:22Z

1

This should do it:

SELECT
array_to_json(array_agg(row_to_json(tb_data))) AS data FROM 
(
    SELECT
    tb_plan.id,
    tb_plan.title,
    array_to_json(array_agg(row_to_json(tb_action))) AS actions
    FROM plan tb_plan
    INNER JOIN "action" tb_action ON tb_plan.id = tb_action.planid
    GROUP BY 1,2
) tb_data

Here's the documentation about the postgresql's json functions used in this query.

edited Sep 28, 2022 at 21:38

answered Jan 6, 2020 at 16:58

juan_carlos_yl

74010 silver badges15 bronze badges

Comments

Mafor · Accepted Answer · 2020-01-06 18:36:02Z

0

Something like this should do the job:

SELECT json_agg(data)
FROM
  (SELECT p.id,
          p.title,
          json_agg(a) FILTER (WHERE a.plan_id IS NOT NULL) AS actions
   FROM PLAN p
   LEFT JOIN action a ON a.plan_id = p.id
   GROUP BY p.id,
            p.title) data;

[
  {
    "id": 1,
    "title": "test plan 1",
    "actions": [
      {
        "id": 1,
        "title": "test action 1",
        "plan_id": 1
      },
      {
        "id": 2,
        "title": "test action 2",
        "plan_id": 1
      }
    ]
  },
  {
    "id": 2,
    "title": "test plan 2",
    "actions": [
      {
        "id": 3,
        "title": "test action 3",
        "plan_id": 2
      },
      {
        "id": 4,
        "title": "test action 4",
        "plan_id": 2
      }
    ]
  }
]

edited Jan 6, 2020 at 18:36

answered Jan 6, 2020 at 17:27

Mafor

10.9k2 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

How to get an array subquery using postgress sql?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related