CODEWITHSUNDEEP
postgresqlpostgresql-9.4
YinAqu
YinAqu

Reputation: 43

Return a range of elements from an array in Postgres json

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?

Upvotes: 4

Views: 3046

Answers (4)

Tregoreg
Tregoreg

Reputation: 22166

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)

Upvotes: 5

ndpu
ndpu

Reputation: 22561

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)

Upvotes: 2

user330315
user330315

Reputation:

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.

Upvotes: 3

klin
klin

Reputation: 121544

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)

Upvotes: 1

Related Questions