Return a range of elements from an array in Postgres json

Question

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?

Thank all of you guys, all your answers are helpful at all. Furthermore, the element amount of my array in practice is over 20000, would anyone tell me which way best performance, tks. — YinAqu
– YinAqu, Commented Nov 2, 2016 at 10:41

Tregoreg · Accepted Answer · 2020-03-17 19:13:45Z

6

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)

answered Mar 17, 2020 at 19:13

Tregoreg

22.9k15 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 4 · Accepted Answer · 2016-11-02 10:25:24Z

4

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.

edited Nov 2, 2016 at 10:25

answered Nov 2, 2016 at 10:05

user330315

Comments

ndpu · Accepted Answer · 2016-11-02 10:27:55Z

3

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)

edited Nov 2, 2016 at 10:27

answered Nov 2, 2016 at 10:06

ndpu

22.6k6 gold badges61 silver badges72 bronze badges

5 Comments

YinAqu Over a year ago

Would you tell me which way better performance?My array is very large.

ndpu Over a year ago

@YinAqu with ordinality shold be faster (no explicit joins to get a row number)

YinAqu Over a year ago

Tks, would u plz check this sql for me, the sub query is fault grammar. select value from json_array_elements(select result_json from hfj_search where search_uuid='16aa6bd4-0b8b-403b-b165-efaab863bf7e') with ordinality where ordinality between 2 and 3;

ndpu Over a year ago

@YinAqu try with additional parentheses to subquery: select value from json_array_elements( (subquery in parentheses) ) with ordinality where ordinality between 2 and 3;

YinAqu Over a year ago

Require explicit type conversion, this works:

select value from json_array_elements( (subquery in parentheses)::json ) with ordinality where ordinality between 2 and 3;

klin · Accepted Answer · 2016-11-02 10:07:06Z

1

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)

answered Nov 2, 2016 at 10:07

klin

123k15 gold badges240 silver badges262 bronze badges

Collectives™ on Stack Overflow

Return a range of elements from an array in Postgres json

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related