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

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 3

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.