How to cast json array to text array?

Question

This workaround not works

CREATE FUNCTION json_array_castext(json) RETURNS text[] AS $f$
  SELECT array_agg(x::text) FROM json_array_elements($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

-- Problem:
SELECT 'hello'='hello';  -- true...
SELECT  (json_array_castext('["hello","world"]'))[1] = 'hello'; -- false!

So, how to obtain real array of text?

PS: with the supposed "first class citizen" JSONb, the same problem.

---

Edit: after @OtoShavadze good answer (the comment solved!), a manifest for PostgreSQL developers: Why x::text is not a cast? (using pg 9.5.6) and why it not generates an warning or an error?

Answer 1

try json_array_elements_text instead of json_array_elements, and you don't need explicit casting to text (x::text), so you can use:

CREATE or replace FUNCTION json_array_castext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

For your additional question

Why x::text is not a cast?

This is cast and because of this, its not giving any error, but when casting json string to text like this: ::text, postgres adds quotes to value.

Just for testing purposes, lets change your function to original again (as it is in your question) and try:

SELECT  
(json_array_castext('["hello","world"]'))[1] = 'hello',
(json_array_castext('["hello","world"]'))[1],
'hello'

As you see, (json_array_castext('["hello","world"]'))[1] gives "hello" instead of hello. and this was why you got false when comparing those values.

Answer 2

For this ugly behaviour of PostgreSQL, there are an ugly cast workaround, the operator #>>'{}':

CREATE or replace FUNCTION json_array_castext(json) RETURNS text[] AS $f$
    SELECT array_agg(x#>>'{}') FROM json_array_elements($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

SELECT  (json_array_castext('["hello","world"]'))[1] = 'hello'; -- true!

---

(edit) Year 2020, pg v12 performance check

We expect that specialized function json_array_elements_text() is better tham user-defined casting... But, how much better? 2 times? 20 times... or only a few percent?
And sometmes we can't use it, so, there are some loss of performance?

Preparing the test:

CREATE TABLE j_array_test AS -- JSON
  SELECT   array_to_json(array[x,10000,2222222,33333333,99999,y]) AS j
  FROM generate_series(1, 1900) t1(x), generate_series(1, 1900) t2(y);

CREATE TABLE jb_array_test AS --JSONb
  SELECT   to_jsonb(array[x,10000,2222222,33333333,99999,y]) AS j
  FROM generate_series(1, 1900) t1(x), generate_series(1, 1900) t2(y);

CREATE FUNCTION ...

Function names:

• j_op_cast(json) use array_agg(x#>>'{}') FROM json_array_elements($1)
• jb_op_cast(jsonb) use array_agg(x#>>'{}') FROM jsonb_array_elements($1)
• j_func_cast(json) use array_agg(x) FROM json_array_elements_text($1)
• jb_func_cast(jsonb) use array_agg(x) FROM jsonb_array_elements_text($1)

RESULTS: All results are near the same, the reported differences are perceptible only after some billions (~3610000) of function calls. For few thousands of calls they are equal-perfornance (!).

EXPLAIN ANALYZE select j_op_cast(j) from j_array_test; -- ~35000
EXPLAIN ANALYZE select j_func_cast(j) from j_array_test;  -- ~28000
-- Conclusion: about average time json_array_elements_text is ~22%  faster.
-- calculated as 200*(35000.-28000)/(28000+35000)

EXPLAIN ANALYZE select jb_op_cast(j) from jb_array_test; -- ~45000
EXPLAIN ANALYZE select jb_func_cast(j) from jb_array_test;  -- ~37000
-- Conclusion: about average time json_array_elements_text is ~20%  faster.
-- calculated as 200*(45000.-37000)/(45000+37000)

For both, JSON and JSONb, the performance difference is in the order of 20%, so in general (e.g. report or microservice output) it is negligible.

As expected JSON cast to text is faster than JSONB cast, because JSON is internally text and JSONB not.

---

PS: using PostgreSQL 12.4 on Ubuntu 20 LTS, virtual machine.

Answer 3

CREATE or replace FUNCTION json_to_array(json) RETURNS text[] AS $f$
  SELECT coalesce(array_agg(x), 
    CASE WHEN $1 is null THEN null ELSE ARRAY[]::text[] END)
  FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

Test cases:

• select json_to_array('["abc"]') => one element array
• select json_to_array('[]') => an empty array
• select json_to_array(null) => null

Answer 4

Oto's answer was a lifesaver, but it did have one boundary case that had me racking my brain. Due to the lossy nature of the cast, it works perfectly except in the case where you've got an empty json array. In that case you would expect an empty array to be returned, but it actually returns nothing. As a workaround, if you just concatenate the return value with an empty array it will have no affect in cases where there is actually a return, but do the right thing when you've got an empty array. Here's the updated SQL functions (for both json and jsonb) that implement the workaround.

CREATE or replace FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE or replace FUNCTION jsonb_array_casttext(jsonb) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

There are a few peculiarities like this one that point to the rough edges at integrating a document database into a mature relational one, but Postgres does an admirable job at handling most of them.

Answer 5

The optimal conversion function for Postgres 9.6 or later is this:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

The ARRAY constructor being cheaper than array_agg(). See:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

It's also kind of important to mark it as PARALLEL SAFE.

See the step-by-step explanation in this earlier answer on dba.SE:

• How to turn JSON array into Postgres array?

Answer 6

In my case it helped to have the result reflect 3 states, null, empty text array and non-empty text array depending on the input. Hopefully this will be useful to someone.

CREATE OR REPLACE FUNCTION json_array_text_array(JSON)
  RETURNS TEXT [] AS $$
DECLARE
  result TEXT [];
BEGIN
  IF $1 ISNULL
  THEN
    result := NULL;
  ELSEIF json_array_length($1) = 0
    THEN
      result := ARRAY [] :: TEXT [];
  ELSE
    SELECT array_agg(x) FROM json_array_elements_text($1) t(x) INTO result;
  END IF;
  RETURN result;
END;
$$
LANGUAGE plpgsql
IMMUTABLE
STRICT;

Answer 7

table

CREATE TABLE IF NOT EXISTS public.j
(
    id integer,
    data jsonb
)

most simple solution I found

SELECT j.id, array_agg(d.elem::int2) AS ar_values
FROM j
CROSS JOIN LATERAL jsonb_array_elements(j.data) k
CROSS JOIN LATERAL jsonb_array_elements_text(k->'ar') AS d(elem)
GROUP BY j.id;

can also be simplified

SELECT j.id, array_agg(d.elem::int2) AS ar_values
FROM j
, LATERAL jsonb_array_elements(j.data) k
, LATERAL jsonb_array_elements_text(k->'ar') AS d(elem)
GROUP BY j.id;