How to cast json array to text array?

user_287948 0
Viewed 12735

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?

postgresql arrays json
5 Answers
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
user_6965090

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;
user_9794780
Related