mysql - 使用 SQL 将 json 数据列表字段转换为列

Question

目前我在我的 DB(mariaDB) 中有一个名为 data 的列，其中包含一个 json 列表：

ID   | data
1    | '["section1","section2","section3"]'
2    | '["section2","section4"]'

我想把它变成这样的东西：

id   | section1     | section2     | section3         | section4
1    | 1            | 1            | 1                | 0
2    | 0            | 1            | 0                | 1

这意味着所有字段中的所有可能值都将成为列并获取值1，如果该列表项相应地存在于行中，0如果不存在。

有没有办法通过查询来转换它？

标签： mysqlsqlarraysjsonmariadb

Answer 1

SELECT 
    id, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section1')) section1, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section2')) section2, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section3')) section3, 
    JSON_LENGTH(JSON_SEARCH(data, 'all', 'section4')) section4 
from test;

如果您需要零而不是 NULL，则使用 COALESCE() 包装表达式。

如果单独的data值不能包含重复的值（这是由根据约束提供的），或者如果您不需要值数量但它们的存在，您可以使用

select 
    id, 
    JSON_CONTAINS(data, '"section1"') section1, 
    JSON_CONTAINS(data, '"section2"') section2, 
    JSON_CONTAINS(data, '"section3"') section3, 
    JSON_CONTAINS(data, '"section4"') section4 
from test;

小提琴

PS. If the list of possible sectionX values is indefinite or dynamic then you must use stored procedure with dynamic SQL.