postgresql 用于从json类型列中筛选数据的Sql

xlpyo6sf 于 2022-11-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(125)

我有一个表A，其中有一个类型为json的列，该列接收一个日期数组。我需要从表A中获取所有女性记录。我需要从表A中的每条记录返回一个特定的日期。

----------------------------------------------------------------------------------- 
table A
-----------------------------------------------------------------------------------
gender (string)      date (json)
-----------------------------------------------------------------------------------
feminine             {"2022-01-01": "A1", "2022-01-02": "A2", "2022-01-03": "A3" } 
masculine            {"2022-01-01": "B1", "2022-01-02": "B2", "2022-01-03": "B3" } 
feminine             {"2022-01-01": "C1", "2022-01-02": "C2", "2022-01-03": "C3" } 
masculine            {"2022-01-01": "D1", "2022-01-02": "D2", "2022-01-03": "D3" } 
-----------------------------------------------------------------------------------

我需要一个返回以下信息的查询：

feminine   "2022-01-01": "A1"
feminine   "2022-01-01": "C1"

仅限女性生产线和日期2022-01-01

postgresql

来源：https://stackoverflow.com/questions/74240875/sql-to-filter-data-from-a-json-type-column

2条答案

按热度按时间

h79rfbju1#

SELECT gender, '"2022-01-01": ' || date->'2022-01-01'
  FROM A
 WHERE gender = 'feminine'

请参阅dbfiddle

赞(0）回复(0）举报 2022-11-04

qvsjd97n2#

一个选项是使用json_each()函数，例如

SELECT gender, CONCAT('"',key,'" : ',value) AS date
  FROM A
 CROSS JOIN LATERAL json_each(date) AS j
 WHERE gender = 'feminine'
   AND key = '2022-01-01'

Demo

赞(0）回复(0）举报 2022-11-04

我来回答

postgresql 用于从json类型列中筛选数据的Sql

2条答案

相关问题

热门标签

最新问答