我有一个存储数据的表,如下所示:
id | data
serial | jsonb
---------+-----------------------------------------------
1 | {"files": [{"date": "2016-05-20", "name": "a"},{"date": "2016-05-21", "name": "b"}]}
2 | {"files": [{"date": "2015-04-02", "name": "c"}]}
3 | {"files": [{"date": "2016-02-12", "name": "d"},{"date": "2016-01-27", "name": "e"}]}
4 | {"files": []}
我有以下JSON文件:
{
"files": [
{"date": "2016-05-20", "name": "Test 1"},
{"date": "2016-05-21", "name": "Test 2"},
{"date": "2016-05-22", "name": "Test 3"}
]
}
我想显示与id
表中的列关联的不同日期,并忽略没有可用日期的行(结果中不显示第4行):
id | date
----+-----------
1 | 2016-05-20
1 | 2016-05-21
2 | 2015-04-02
3 | 2016-02-12
3 | 2016-01-27
在这里,我编写了查询,但未返回任何内容:
SELECT
id,
"data" -> 'files' ->> 'date' AS "date"
FROM myTable
WHERE "data" -> 'files' @> '{"date"}'::jsonb ;
试试这个:
SELECT id, json_array_elements((data->'files')::json)->>'date' as "date" FROM myTable
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句