如何在PostgreSQL 9.3中查询JSON数组? [英] How to query an array of JSON in PostgreSQL 9.3?

问题描述

我创建了一个名为"delivery"的表，其中一个字段是JSON数组.

I created a table named 'delivery' and one of its field is an array of JSON.

CREATE TABLE delivery
(
  id text NOT NULL,
  date timestamp with time zone NOT NULL,
  items json NOT NULL,
  CONSTRAINT delivery_pkey PRIMARY KEY (id)
)

这是示例数据:

"1", "2012-01-08 20:54:38.541989+00", "[ {"id":1, "qty": 10 }, {"id":2, "qty": 300}]"
"2", "2012-01-08 22:44:10.016285+00", "[ {"id":1, "qty": 200}]"

如何获取项目"字段中"id"等于1的行?

How can I get the rows where the 'id' within the 'items' field is equal to 1?

推荐答案

您可以使用子查询和json功能来做到这一点:

You can do it using subquery and json fucntions like this:

SELECT * FROM delivery
WHERE '1' in (
   select a->>'id' from json_array_elements(items) a
)

这是SQL 小提琴.

这篇关于如何在PostgreSQL 9.3中查询JSON数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！