CODEWITHSUNDEEP
sqlarraysjsonpostgresql
whats in a name
whats in a name

Reputation: 87

How to get an array subquery using postgress sql?

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.

Upvotes: 1

Views: 134

Answers (3)

juan_carlos_yl
juan_carlos_yl

Reputation: 720

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.

Upvotes: 1

Mafor
Mafor

Reputation: 10671

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
      }
    ]
  }
]

Upvotes: 0

Jim Jones
Jim Jones

Reputation: 19613

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)

Upvotes: 2

Related Questions