Postgres SQL查询,返回ID为动态键的JSON对象

汤姆

我想创建一个(postgres)SQL查询,该查询返回带有动态键的(JSON)对象。因此,我创建了带有一些值的示例表。

CREATE TABLE foods (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE nutrients (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE foods_nutrients (
  food_id int REFERENCES foods(id) ON UPDATE CASCADE ON DELETE CASCADE,
  nutrient_id int REFERENCES nutrients(id) ON UPDATE CASCADE ON DELETE CASCADE,
  amount DECIMAL NOT NULL,
  CONSTRAINT food_nutrient_pk PRIMARY KEY (food_id, nutrient_id)
);

INSERT INTO foods(name)
VALUES  ('Apple'),
        ('Banana');
INSERT INTO nutrients(name)
VALUES  ('Carbohydrates'),
        ('Protein'),
        ('Fat');
INSERT INTO foods_nutrients(food_id, nutrient_id, amount)
VALUES  (1, 1, 14.0),
        (1, 2, 0.3),
        (1, 3, 0.2),
        (2, 1, 23.7),
        (2, 2, 1.1);

如果可能的话,查询结果应类似于此JSON。或至少足够接近。我认为主要问题是以营养素ID为键的对象。

[
  {
    "id": 1,
    "name": "Apple",
    "nutrients": {
      "1": 14.0,
      "2": 0.3,
      "3": 0.2
    }
  },
  {
    "id": 2,
    "name": "Banana",
    "nutrients": {
      "1": 23.7,
      "2": 1.1
    }
  }
]
巴巴罗斯·奥赞

主要JSON_BUILD_OBJECT()需要函数构造之间的组合idnamenutrients的值。但是nutrients需要一个将要使用的操作,JSON_OBJECT_AGG()以获取更复杂的对象。因此,请考虑使用

WITH fn1 AS
(
SELECT fn.food_id, f.name,
       JSON_OBJECT_AGG( nutrient_id, amount ) AS nutrients                        
  FROM foods_nutrients fn 
  JOIN foods f 
    ON fn.food_id=f.id
  JOIN nutrients n 
    ON fn.nutrient_id=n.id
 GROUP BY fn.food_id, f.name  
)
SELECT JSON_AGG(
                JSON_BUILD_OBJECT( 'id', food_id, 
                                   'name', name,
                                   'nutrients', nutrients) 
                         ) AS js                
  FROM fn1

Demo

顺便说一句,使用JSONB这些函数的等效项以及JSONB_PRETTY()嵌套我们的当前结果将产生一个很好的设计Demo,就像问题中的格式一样:

[
    {
        "id": 1,
        "name": "Apple",
        "nutrients": {
            "1": 14.0,
            "2": 0.3,
            "3": 0.2
        }
    },
    {
        "id": 2,
        "name": "Banana",
        "nutrients": {
            "1": 23.7,
            "2": 1.1
        }
    }
]

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章