CODEWITHSUNDEEP
arraysjsonpostgresqlpostgresql-9.3
Max Maddy
Max Maddy

Reputation: 349

How to convert json array into Postgres int array

I have a scenario where I need to convert a json array into a Postgres int array and query it for the result. Below is my table:

id data
1 {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
2 {"bookIds" : [4,5,6,7], "storeIds": [1,3]}
3 {"bookIds" : [11,12,10,9], "storeIds": [4,3]}

I want to convert booksId array into int array and later query it. Is it possible in Postgres 9.3? I know 9.4 + provides much more JSON support but I can't update my db at the moment.

The below query gives me an error:

Select data::json->>'bookIds' :: int[] from table

ERROR:  malformed array literal: "bookIds"
LINE 1: Select data::json->>'bookIds' :: int[] from table

Is it possible to query elements inside json array in Postgres 9.3?

Upvotes: 22

Views: 52195

Answers (6)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658907

Since Postgres 10, the answer to your title is simple and fast with json_to_record():

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL json_to_record(t.data) AS d("bookIds" int[]) ON true;

See:

The answer to your question is "Yes".

Is it possible to query elements inside json array in Postgres 9.3?

How to do it exactly depends on how you want to "query" those elements exactly. Typically, you don't have to convert the JSON array to "query" it. In modern Postgres versions there are even more options.

Upvotes: 0

simUser
simUser

Reputation: 840

I suggest all in one line

SELECT translate('[1,2,3,4]'::jsonb::text, '[]', '{}')::int[]

returns an integer array

{1,2,3,4}::int[]

and within a query...

SELECT translate(field->>'mykey', '[]', '{}')::int[]

nb! the double arrow ->> where field->>'mykey' will return a ::text

Upvotes: 4

klin
klin

Reputation: 121889

The setup in the question should look like this:

create table a_table (id int, data json);
insert into a_table values
(1, '{"bookIds": [1,2,3,5], "storeIds": [2,3]}'), 
(2, '{"bookIds": [4,5,6,7], "storeIds": [1,3]}'),
(3, '{"bookIds": [11,12,10,9], "storeIds": [4,3]}');

Note the proper syntax of json values.

You can use the function json_array_elements()

select id, array_agg(book_id::text::int) as book_ids
from a_table
cross join json_array_elements(data->'bookIds') a(book_id)
group by 1
order by 1;

 id |   book_ids
----+--------------
  1 | {1,2,3,5}
  2 | {4,5,6,7}
  3 | {11,12,10,9}
(3 rows)

Use any() to search for an element in the arrays, e.g.:

select *
from (
    select id, array_agg(book_id::text::int) as book_ids
    from a_table
    cross join json_array_elements(data->'bookIds') a(book_id)
    group by 1
    ) s
where 
    1 = any(book_ids) or
    11 = any(book_ids);

 id |   book_ids
----+--------------
  1 | {1,2,3,5}
  3 | {11,12,10,9}
(2 rows)

Read also about <@ operator.

You can also search in json array (without converting it to int array) by examine its elements, e.g.:

select t.*
from a_table t
cross join json_array_elements(data->'bookIds') a(book_id)
where book_id::text::int in (1, 11);

 id |                     data                      
----+-----------------------------------------------
  1 | {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
  3 | {"bookIds" : [11,12,10,9], "storeIds": [4,3]}
(2 rows)

Generic functions

You might want to have generic tools for conversion from JSON arrays. Use the function to convert a JSON array of arbitrary strings:

create or replace function jsonb_text_array(jsonb)
returns text[] language sql immutable as $$
    select array(select jsonb_array_elements_text($1));
$$;

The resulting array is text[] that can be cast to int[]. However, when dealing specifically with integer arrays, this function should be significantly faster:

create or replace function jsonb_integer_array(jsonb)
returns integer[] language sql immutable as $$
    select translate($1::text, '[]', '{}')::integer[];
$$;

select id, jsonb_integer_array(data::jsonb->'bookIds') as book_ids
from a_table;

 id |   book_ids
----+--------------
  1 | {1,2,3,5}
  2 | {4,5,6,7}
  3 | {11,12,10,9}
(3 rows)

Note that JSONB has been available since Postgres 9.4.

Upvotes: 28

Alferd Nobel
Alferd Nobel

Reputation: 3979

In my case I had to cast json data stored in a table col to pg array format and this was handy :

-- username is the table column, which has values like ["john","pete","kat"]

select id, ARRAY(SELECT json_array_elements_text((username)::json)) usernames
from public.table-name;

-- this produces : {john,pete,kat}

Upvotes: 11

Joel B
Joel B

Reputation: 13140

These two functions (for json/jsonb) modified from a fantastic answer to this question work perfectly

CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

You can use them as follows:

SELECT json_array_castint('[1,2,3]')

Which gives the expected return {1,2,3} as in integer[]. If you wonder why I'm concatenating with an empty array in each of the SELECT statement it's because the cast is lossy and without it, if you try to cast an empty json/jsonb array to an integer[] you'll get no return (not desired) instead of an empty array (as expected). With the above method when you do

SELECT json_array_castint('[]')

You'll get {} instead of nothing. See here for more on why I added that.

Upvotes: 9

Jorge Campos
Jorge Campos

Reputation: 23381

I would go a bit simpler:

select * from
(
select t.id, value::text::int as bookvalue
  from testjson t, json_array_elements(t.data->'bookIds')
) as t
where bookvalue in (1,11)

See it working here: http://sqlfiddle.com/#!15/e69aa/37

Upvotes: 3

Related Questions