Returning a json array slice in PostgreSQL

Question

Postgres 9.5

Given a table with one field of type json, about 700 rows, and where each row has about 4,000 elements in a single array...

my_db_field:

[0.44577, 0.4855, 0.45429, 0.54437,...]
[0.45012, 0.48698, 0.45715, 0.55337,...]
[0.47347, 0.49156, 0.46079, 0.56818,...]
[0.4936, 0.49835, 0.46086, 0.58195,...]
[0.51068, 0.50511, 0.46228, 0.59482,...]

The PostgreSQL docs show how you can query for a single element inside an array.

 SELECT my_db_field->2 AS test FROM my_db_table

results:

test (of type json)
--------------------
0.4855
0.48698
0.49156
etc.

What I would like to do, though, is select multiple elements in the array and return that as an array in the same format the rows are. By multiple, I mean around 300 elements in the array; e.g., from element 0 to element 300. Does Postgres have a nice syntax for such a query?

Answer 1

select  array_to_json 
        (
           (select  array_agg(my_db_field->n order by n) 
            from    generate_series(0,least(300,json_array_length(my_db_field))-1) gs(n) 
            )
        )

from    my_db_table

---

This solution (the original in this answer) most likely does not guarantee the order of the elements

select (array(select json_array_elements(my_db_field)))[1:300]
from    my_db_table

Answer 2

In PostgreSQL 12, you can obtain JSONB array slice using the jsonb_path_query_array function:

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

Answer 3

We don't know enough about your schema and the json field do to it, but essentially create a new table

CREATE TABLE test_array (
  test_id int       REFERENCES test
  idx     smallint,
  elem    text
  PRIMARY KEY (test_id, idx)
);

prune the json-array out of the json object and move it to the new table. Then you could just do

SELECT * FROM test
JOIN test_array
  USING ( test_id )
WHERE idx < 300

JSON is fine for storing, but I'm questioning whether or not you've outgrown its utility at this point.