postgresql - Postgres 将带双引号的字符串拆分为多行？

Question

我有一个看起来像这样的表:

|---------------------|-----------------------------------------------|
|        ID (int)     |     Location (string)                         |
|---------------------|-----------------------------------------------|
|         1           |    ["Humboldt, TN","Medina, TN","Milan, TN"]  |
|---------------------|-----------------------------------------------|

我想把它变成一个看起来像这样的表格:

|---------------------|-----------------------------------------------|
|        ID (int)     |     Location (string)                         |
|---------------------|-----------------------------------------------|
|         1           |    Humboldt, TN                               |
|---------------------|-----------------------------------------------|
|         1           |    Medina, TN                                 |
|---------------------|-----------------------------------------------|
|         1           |    Milan, TN                                  |
|---------------------|-----------------------------------------------|

查询是什么样的？我想我可以在 , 上拆分文本，但我需要一种方法来忽略双引号内的逗号。

Answer 1

location 字符串类似于文本数组。将其转换为 text[] 并取消嵌套:

with my_data(id, location) as (
values 
    (1, '["Humboldt, TN","Medina, TN","Milan, TN"]')
)

select id, unnest(format('{%s}', trim(location, '[]'))::text[]) as location
from my_data

 id |   location   
----+--------------
  1 | Humboldt, TN
  1 | Medina, TN
  1 | Milan, TN
(3 rows)

或者更简单，将字符串转换为 jsonb 并使用 jsonb_array_elements_text():

with my_data(id, location) as (
values 
    (1, '["Humboldt, TN","Medina, TN","Milan, TN"]')
)

select id, jsonb_array_elements_text(location::jsonb) as location
from my_data

Db<>fiddle.