Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 248
Leo Brito Avatar asked Jun 30 '16 15:06

Leo Brito


2 Answers

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); 
like image 64
Paul A Jungwirth Avatar answered Oct 13 '22 00:10

Paul A Jungwirth


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)
like image 31
Tregoreg Avatar answered Oct 13 '22 01:10

Tregoreg