时间戳

首页 > 解决方案 > 如何使用 postgress sql 获取数组子查询？

sql - 如何使用 postgress sql 获取数组子查询？

问题描述

所以，我需要的是下面的 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'
       }
      ]}
]

所以我有 2 张桌子、计划和行动。两个表之间的关系是计划有很多动作。计划（ID，标题）动作（ID，标题，计划ID）

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

我不确定如何获得每个计划下的相关操作。

标签： sqlarraysjsonpostgresql

解决方案

这些查询可能是您要查找的内容：

数据样本

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);

查询 - 多条 json 记录

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)

查询 - 单个 json 记录

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)

推荐阅读