从包含JSON信息的文本字段中提取数据

Question

我是JSON和PostgreSQL的新手。我的数据如下所示。moviegenre是TEXT字段。

movieGenres

[{'id': 1, 'name': 'Comedy'}]
[{'id': 2, 'name': 'Action'}, {'id': 3, 'name': 'Crime'}, {'id': 4, 'name': 'Drama'}, {'id': 5, 'name': 'Thriller'}]
[{'id': 1, 'name': 'Comedy'}, {'id': 6, 'name': 'Romance'}]
[{'id': 2, 'name': 'Action'}, {'id': 7, 'name': 'Adventure'}, {'id': 4, 'name': 'Drama'}, {'id': 8, 'name': 'Family'}]

我想从这个列表中获得不同类型的列表，比如

{'id': 1, 'name': 'Comedy'}
{'id': 6, 'name': 'Romance'}
{'id': 7, 'name': 'Adventure'}

在单个字段中有多个类型映射到ID，我试图从"Genres"中提取唯一的ID和类型名称。字段。我如何在postgresql中做到这一点?

Answer 1

您可以像这样规范化您的数据:

select t.movieid, 
(l ->> 'id')::integer as genre_id, l ->> 'name' as genre_name
from the_table t
cross join lateral jsonb_array_elements(replace(genres, '''', '"')::jsonb) l;

和BTW字段genres是无效的JSON，必须用双引号替换单引号