gpt4 book ai didi

sql - 如何从我的数据中返回一个 jsonb 数组和对象数组?

转载 作者:行者123 更新时间:2023-11-29 12:11:38 27 4
gpt4 key购买 nike

我有下表:

CREATE TABLE mytable (
id serial PRIMARY KEY
, employee text UNIQUE NOT NULL
, data jsonb
);

具有以下数据:

INSERT INTO mytable (employee, data)
VALUES
('Jim', '{"sales_tv": [{"value": 10, "yr": "2010", "loc": "us"}, {"value": 5, "yr": "2011", "loc": "europe"}, {"value": 40, "yr": "2012", "loc": "asia"}], "sales_radio": [{"value": 11, "yr": "2010", "loc": "us"}, {"value": 8, "yr": "2011", "loc": "china"}, {"value": 76, "yr": "2012", "loc": "us"}], "another_key": "another value"}'),
('Rob', '{"sales_radio": [{"value": 7, "yr": "2014", "loc": "japan"}, {"value": 3, "yr": "2009", "loc": "us"}, {"value": 37, "yr": "2011", "loc": "us"}], "sales_tv": [{"value": 4, "yr": "2010", "loc": "us"}, {"value": 18, "yr": "2011", "loc": "europe"}, {"value": 28, "yr": "2012", "loc": "asia"}], "another_key": "another value"}')

请注意,除了“sales_tv”和“sales_radio”之外,还有其他键。对于下面的查询,我只需要关注“sales_tv”和“sales_radio”。

我需要查找 Jim 在 2012 年的所有销售额。任何以“sales_”开头的内容,然后将其放入一个对象中(只需要所售产品的数量和值(value))。例如:

    employee   | sales_
Jim | {"sales_tv": 40, "sales_radio": 76}

我有:

SELECT * FROM mytable,
(SELECT l.key, l.value FROM mytable, lateral jsonb_each_text(data) AS l
WHERE key LIKE 'sales_%') AS a,
jsonb_to_recordset(a.value::jsonb) AS d(yr text, value float)
WHERE mytable.employee = 'Jim'
AND d.yr = '2012'

但我似乎连 Jim 的数据都无法获取。相反,我得到:

employee | key         |  value
-------- |------ | -----
Jim | sales_tv | [{"yr": "2010", "loc": "us", "value": 4}, {"yr": "2011", "loc": "europe", "value": 18}, {"yr": "2012", "loc": "asia", "value": 28}]
Jim | sales_tv | [{"yr": "2010", "loc": "us", "value": 10}, {"yr": "2011", "loc": "europe", "value": 5}, {"yr": "2012", "loc": "asia", "value": 40}]
Jim | sales_radio | [{"yr": "2010", "loc": "us", "value": 11}, {"yr": "2011", "loc": "china", "value": 8}, {"yr": "2012", "loc": "us", "value": 76}]

最佳答案

您将第一个连接的结果视为 JSON,而不是文本字符串,因此使用 jsonb_each()而不是 jsonb_each_text():

SELECT t.employee, json_object_agg(a.k, d.value) AS sales
FROM mytable t
JOIN LATERAL jsonb_each(t.data) a(k,v) ON a.k LIKE 'sales_%'
JOIN LATERAL jsonb_to_recordset(a.v) d(yr text, value float) ON d.yr = '2012'
WHERE t.employee = 'Jim' -- works because employee is unique
GROUP BY 1;

GROUP BY 1GROUP BY t.employee 的简写。
结果:

employee | sales
---------+--------
Jim | '{ "sales_tv" : 40, "sales_radio" : 76 }'

我还理清并简化了您的查询。

json_object_agg()有助于将名称/值对聚合为 JSON 对象。如果需要,可以选择转换为 jsonb - 或者使用 jsonb_object_agg()在 Postgres 9.5 或更高版本中。

使用明确的 JOIN 语法在最明显的位置附加条件。
相同,没有明确的 JOIN 语法:

SELECT t.employee, json_object_agg(a.k, d.value) AS sales
FROM mytable t
, jsonb_each(t.data) a(k,v)
, jsonb_to_recordset(a.v) d(yr text, value float)
WHERE t.employee = 'Jim'
AND a.k LIKE 'sales_%'
AND d.yr = '2012'
GROUP BY 1;

关于sql - 如何从我的数据中返回一个 jsonb 数组和对象数组?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31549705/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com