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

Question

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?

Answer 1

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

Answer 2

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)