As known, in Postgres json, we can get one element at a specified index from an array using this:
["a","b","c"]::json -> 2
The script above return the 3rd element "c".
Then is there any way a range of elements can be returned if I specify a index range such as 0 to 20?
In PostgreSQL 12, I managed to engineer JSONB array slice using the jsonb_path_query_array function as follows:
SELECT jsonb_path_query_array('["a","b","c","d","e","f"]', '$[2 to 4]');
jsonb_path_query_array
------------------------
["c", "d", "e"]
(1 row)
You can convert the json array to a real array by changing the text representation from '["a","b","c"]' to '{"a","b","c"}' and cast the result to text[].
Then you can use the usual Postgres array subscript:
select (translate('["a","b","c"]'::json::text, '[]','{}')::text[])[1:2]
returns
{a,b}
Note that native Postgres arrays are one-based (first element has the index 1), unlike the JSON arrays which are zero-based.
With json_array_elements and row_number for example:
db=> select value
from (select row_number() over (), value
from json_array_elements('["a","b","c"]'::json)
) as t
where row_number between 2 and 3;
value
-------
"b"
"c"
(2 rows)
or by using WITH ORDINALITY
db=> select value
from json_array_elements('["a","b","c"]'::json)
with ordinality
where ordinality between 2 and 3;
value
-------
"b"
"c"
(2 rows)
select value from json_array_elements( (subquery in parentheses) ) with ordinality where ordinality between 2 and 3;select value from json_array_elements( (subquery in parentheses)::json ) with ordinality where ordinality between 2 and 3;You can create your own function (as there is no such a feature in Postgres):
create or replace function json_sub_array(json_array json, from_pos int, to_pos int)
returns json language sql as $$
select json_agg(value)
from json_array_elements(json_array) with ordinality
where ordinality-1 between from_pos and to_pos
$$;
select json_sub_array('["a","b","c","d"]'::json, 1, 2);
json_sub_array
----------------
["b", "c"]
(1 row)
