I have a postgresql table containing a column (movies) with json array. The column type is text. Below is example of table:
| name | movies |
|---|---|
| bob | ['movie1', 'movie2'] |
| mary | ['movie1', 'movie3'] |
How can I unnest the above table to look like below:
| name | movie |
|---|---|
| bob | movie1 |
| bob | movie2 |
| mary | movie1 |
| mary | movie3 |
Also note that the elements in the json array are single quoted
Im using postgresql database on AWS RDS engine version 10.17.
Thanks in advance
That is not JSON, that is "something vaguely inspired by JSON". we don't know how it will deal with things like apostrophes in the titles, or non ASCII characters, or any of the other things that an actual standard should specify but something vaguely inspired by a standard doesn't.
If you want to ignore such niceties and make something that works on this one example, we could suppress the characters '[] (done by regexp_replace) and then split/unnest on commas followed by optional space (done by regexp_split_to_table).
with t as (select 'bob' name ,$$['movie1', 'movie2']$$ movies union select 'mary',$$['movie1', 'movie3']$$)
select name, movie from t, regexp_split_to_table(regexp_replace(movies,$$['\[\]]$$,$$$$,'g'),', ?') g(movie);
Another slightly more resilient option would be to swap ' for " then use an actual JSON parser:
with t as (select 'bob' name ,$$['lions, and tigers, and bears', 'movie2']$$ movies union select 'mary',$$['movie1','movie3']$$)
select name, movie from t, jsonb_array_elements_text(regexp_replace(movies,$$'$$,$$"$$,'g')::jsonb) g(movie);
