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                                  |
|---------------------|-----------------------------------------------|

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

标签： postgresql

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<>小提琴。