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?

• Evan Carroll: I would look at restructuring the schema here.
• mg1075: @EvanCarroll - anything in particular you had in mind in that regard?
• Evan Carroll: We don't know enough about your schema and the json field do to it, but essentially create a new table test_id int, idx int, elem text. 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.
• Evan Carroll: I've made my comment an answer if you decide to go down that route.

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

• David דודו Markovitz: Please check........
• David דודו Markovitz: Please handle with care, also I assume the elements order will be preserved, I'm afraid it is not guaranteed. I'll have to check it later on.
• mg1075: Cool! The only issue is this returns each row as an object instead of an array. Seems hacky, but by adding a json_build_array function, then the result for each row is an array. SELECT json_build_array((array(select json_array_elements(my_db_field)))[1:300])->0 FROM my_db_table
• mg1075: re: elements order not preserved - hmmm, that would be a concern, although I don't see any dis-ordering right now of return rows or of values inside the arrays.
• David דודו Markovitz: @EvanCarroll - It seems reasonable that the internal implementation of json_array_elements and array will treat elements by their order but their is no order clause in this solution and as far as I know there is no guarantee in the documentation for such behaviour. It doesn't different in concept from inserting rows to a table and expecting that a select will return the rows in the same order they were inserted.

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.