我有 PostgreSQL 11.5 與這個 jsonb 資料類似的東西：
[{"name":"$.publishedMonth", "value":"04"},{"name":"$.publishedYear","value":"1972"}]
[{"name":"$.publishedMonth", "value":"07"},{"name":"$.publishedYear","value":"2020"}]
我想要的結果是：
| ID | 出版年 |
|---|---|
| 1 | 04-1972 |
| 2 | 07-2020 |
SELECT b.field_value AS publishedMonthYear, count(*) FROM
( SELECT * FROM (SELECT (jsonb_array_elements(result)::jsonb)->>'name' field_name,
(jsonb_array_elements(result)::jsonb)->>'value' field_value,
FROM books WHERE bookstore_id='3') a
WHERE a.field_name in('$.publishedMonth','$.publishedYear')) b GROUP BY b.field_value
預先感謝您的任何幫助
uj5u.com熱心網友回復：
示例表和資料結構：dbfiddle
select
id,
string_agg(
value->>'value',
'-' order by value->>'name'
)
from
book b
cross join jsonb_array_elements(b.result::jsonb) e
group by id
having array_agg(value->>'name' order by value->>'name') = '{$.publishedMonth,$.publishedYear}'
uj5u.com熱心網友回復：
包括：由聚合的查詢id表中的列books交叉連接JSONB_ARRAY_ELEMENTS()沒有子查詢就足夠了，如
SELECT STRING_AGG(j ->> 'value', '-' ORDER BY j ->> 'name') AS "publishedMonthYear"
FROM books,
JSONB_ARRAY_ELEMENTS(result) AS arr(j)
WHERE bookstore_id = 3
GROUP BY id
考慮按字母順序j ->> 'name'提取值（$.publishedMonth和$.publishedYear）的排序依據。
Demo
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/349938.html
標籤：sql PostgreSQL jsonb
上一篇：如何根據不同表中的值填充表