arraysjsonpostgresql

How to cast json array to text array?

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?

Solution 
  • 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: