How to cast json array to text array?
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.
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.
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 arrayselect json_to_array('[]')
=> an empty arrayselect json_to_array(null)
=> null