Dynamically cast element to JSON array if it is a JSON string in PostgreSQL

Question

Basically, I have to select a value from a JSON object that has an awful format. This value might be a string or a JSON array, and, if it is an array, I should aggregate it's elements into a single, comma-separated string.

So, for example, my table might look like this:

  id  |                 field
------+--------------------------------------
  1   |            {"key": "string"} 
---------------------------------------------
  2   |  {"key": ["array", "of", "strings"]}

and I would need a result like

"string", 
"array, of, strings"

I'm using the following query:

SELECT my_table.id,
       array_to_string(ARRAY(SELECT json_array_elements_text(field -> json_object_keys(field))), ', '),
FROM my_table

to aggregate the JSON array into a string. However, when the value is a string, I get the ERROR: cannot call json_array_elements_text on a scalar. So now the next step seems like casting the value to a JSON array when it's a string (basically just wrap it with brackets).

How would I do such a thing? I've looked at the Postgres doc but see no functions to cast to different JSON types.

I'm using PostgreSQL 10

as a side note, would it be simpler to just use a CASE statement, perhaps using the json_typeof function? — ldeld
– ldeld, Commented Jul 24, 2018 at 14:01

Vao Tsun · Accepted Answer · 2018-07-25 04:35:51Z

1

with c(id,field) as (values(1,'{"key": "string"}'::json),(2, '{"key": ["array", "of", "strings"]}'))
, pg10 as (select id, case when json_typeof(field->'key') != 'array' then json_build_array(field->>'key') else field->'key' end field from c)
, m as (select id, json_array_elements_text(field) field from pg10)
select distinct id, string_agg(field,', ') over (partition by id) field from m;

as you mentioned in comment you need an extra step to take SRF out of case

https://www.db-fiddle.com/f/mr5rWGpC6xRoBvUKwR7RTN/0

edited Jul 25, 2018 at 4:35

answered Jul 24, 2018 at 14:22

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thomas Berger Over a year ago

functions with resultsets are not allowed in CASE, at least in 10.2 not working ..

ldeld Over a year ago

yes, as Thomas commented, this raises a set-returning functions are not allowed in CASE error

Vao Tsun Over a year ago

ah, postgres 10. yes, surely

Collectives™ on Stack Overflow

Dynamically cast element to JSON array if it is a JSON string in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related