CODEWITHSUNDEEP
jsonpostgresql
Leo Brito
Leo Brito

Reputation: 2051

Retrieve first N records of a JSON array with a Postgresql query

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?

Upvotes: 7

Views: 8431

Answers (3)

Lalit Sharma
Lalit Sharma

Reputation: 374

For anyone who will stumble here trying the same thing. Here is what I faced

I had a similar problem, I needed the N result from jsonb field which was containing an array. The result I needed was all the fields of the table and N number of data from the data field where the condition satisfies.

After going through this again and again I found out that the accepted answer aggregates the rows. So if you have a table

id type data status
1 employee {{"age": 29, "name": "EMP 1"}, {"age": 30, "name": "EMP 2"},...} Active
2 manager {{"age": 28, "name": "MNG 1"}, {"age": 30, "name": "MNG 2"},...} Active

and you run the query

select (array_agg(e))[2:3]
from (select json_array_elements(data::json) from <table>) x(e);

then the output will be

data
{{"age": 30, "name": "EMP 2"}, {"age": 28, "name": "MNG 1"}}

which was not needed in my case, What I needed was n data for each individual row where the condition satisfies e.g.

data
{{"age": 29, "name": "EMP 1"},{"age": 30, "name": "EMP 2"}}
{{"age": 28, "name": "MNG 1"},{"age": 30, "name": "MNG 2"}}

so after searching a little and going through the link provided by @Paul A Jungwirth in the accepted answer. I found out that this can also be achieved by

select (ARRAY(select json_array_elements_text(data::json)))[0:2]

and the result will give you the n number of data from the jsonb field where the condition satisfies, you can also access other fields with it as well. Like let's say you want id and n number of data out of this table then you can do that just by adding id in the select query. (I was unable to get "id" in the query in the accepted answer)

select id, (ARRAY(select json_array_elements(data::json)))[0:2] from table where condition

will give output

id data
1 {{"age": 29, "name": "EMP 1"},{"age": 30, "name": "EMP 2"}}
2 {{"age": 28, "name": "MNG 1"},{"age": 30, "name": "MNG 2"}}

Hope this will be helpful to someone.

Upvotes: 1

Tregoreg
Tregoreg

Reputation: 22166

In PostgreSQL 12, you can do:

SELECT jsonb_path_query_array('["a","b","c","d","e","f"]', '$[0 to 3]');
 jsonb_path_query_array
------------------------
 ["a", "b", "c", "d"]
(1 row)

Upvotes: 11

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24541

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);

Upvotes: 8

Related Questions