使用 Postgresql 查询检索 JSON 数组的前 N 条记录 [英] Retrieve first N records of a JSON array with a Postgresql query
问题描述
PostgreSQL 从 9.3 版开始就有一些原生的 JSON 操作.假设你有一个表,my_table
,有一个 json
列,my_json_col
,结构如下:
要检索 my_json_col
的第 n 个元素,您将执行如下操作:SELECT my_json_col->n FROM my_table WHERE ...
.因此,如果 n = 1
,在我的示例中,查询将返回 "id": 2
记录.
我想检索前 n 个元素,例如如果 n = 2
在我的示例中,查询应该返回前两条记录.这可能吗?
我认为你需要 将 JSON 数组转换为常规 Postgres 数组,然后取一部分:
select (array_agg(e))[2:3]from (select json_array_elements('[{"id":1},{"id":2},{"id":3},{"id":4}]'::json)) x(e);
如果你需要结果是JSON,你可以使用array_to_json
:
select array_to_json((array_agg(e))[2:3])from (select json_array_elements('[{"id":1},{"id":2},{"id":3},{"id":4}]'::json)) x(e);
PostgreSQL has some native JSON operations since verison 9.3. Suppose you have a table, my_table
, with a json
column, my_json_col
, structured as follows:
[
{ "id": 1, "some_field": "blabla" },
{ "id": 2, "some_field": "foo" }
...
]
To retrieve the n-th element of my_json_col
, you would execute something like: SELECT my_json_col->n FROM my_table WHERE ...
. So if n = 1
, the query would return the "id": 2
record in my example.
I want to retrieve the first n elements, e.g. if n = 2
the query should return the first two records in my example. Is this possible?
I think you need to convert the JSON array to a regular Postgres array, then take a slice of it:
select (array_agg(e))[2:3]
from (select json_array_elements('[{"id":1},{"id":2},{"id":3},{"id":4}]'::json)) x(e);
If you need the result to be JSON, you can use array_to_json
:
select array_to_json((array_agg(e))[2:3])
from (select json_array_elements('[{"id":1},{"id":2},{"id":3},{"id":4}]'::json)) x(e);
这篇关于使用 Postgresql 查询检索 JSON 数组的前 N 条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!