sql解析json数组
记录一下最近遇到的利用SQL解析Json数组的问题。
·
1.记录一下最近遇到的利用SQL解析Json数组的问题。
- 样例:该Json数组存储在字段名为:productInfo 的字段中。
[{"product_id":"001","ProductName":"样例1","SpareParts":1,"SparePartsQuantity":"2","SparePartsUnitPrice":"3","SparePartsAmount":"4"},
{"product_id":"002","ProductName":"样例2","SpareParts":2,"SparePartsQuantity":"3","SparePartsUnitPrice":"4","SparePartsAmount":"5"},
{"product_id":"003","ProductName":"样例3","SpareParts":3,"SparePartsQuantity":"4","SparePartsUnitPrice":"5","SparePartsAmount":"6"},
{"product_id":"004","ProductName":"样例4","SpareParts":4,"SparePartsQuantity":"5","SparePartsUnitPrice":"6","SparePartsAmount":"7"},
]
- SQL语句
select * from (SELECT
JSON_UNQUOTE ( JSON_EXTRACT ( JSON_EXTRACT ( productInfo , '$[*].product_id' ), CONCAT( '$[', idx, ']' ) ) ) product_id ,
JSON_UNQUOTE ( JSON_EXTRACT ( JSON_EXTRACT ( productInfo , '$[*].ProductName' ), CONCAT( '$[', idx, ']' ) ) ) ProductName,
JSON_UNQUOTE ( JSON_EXTRACT ( JSON_EXTRACT ( productInfo , '$[*].SpareParts' ), CONCAT( '$[', idx, ']' ) ) ) SpareParts ,
JSON_UNQUOTE ( JSON_EXTRACT ( JSON_EXTRACT ( productInfo , '$[*].SparePartsQuantity' ), CONCAT( '$[', idx, ']' ) ) ) SparePartsQuantity ,
JSON_UNQUOTE ( JSON_EXTRACT ( JSON_EXTRACT ( productInfo , '$[*].SparePartsUnitPrice' ), CONCAT( '$[', idx, ']' ) ) ) SparePartsUnitPrice ,
JSON_UNQUOTE ( JSON_EXTRACT ( JSON_EXTRACT ( productInfo , '$[*].SparePartsAmount' ), CONCAT( '$[', idx, ']' ) ) ) SparePartsAmount ,
FROM
table_name1
JOIN (
SELECT
0 AS idx UNION
SELECT
1 AS idx UNION
SELECT
2 AS idx UNION
SELECT
3 AS idx UNION
SELECT
4 AS idx UNION
SELECT
5 AS idx UNION
SELECT
6 AS idx UNION
SELECT
7 AS idx UNION
SELECT
8 AS idx UNION
SELECT
9 AS idx UNION
SELECT
10 AS idx UNION
SELECT
11 AS idx UNION
SELECT
12 AS idx UNION
SELECT
13 AS idx UNION
SELECT
14 AS idx UNION
SELECT
15 AS idx UNION
SELECT
16 AS idx UNION
SELECT
17 AS idx UNION
SELECT
18 AS idx UNION
SELECT
19 AS idx UNION
SELECT
20 AS idx
) AS INDEXES
WHERE
JSON_UNQUOTE ( JSON_EXTRACT ( JSON_EXTRACT ( bjxx, '$[*]._id' ), CONCAT( '$[', idx, ']' ) ) ) is not null
) t
更多推荐
已为社区贡献1条内容
所有评论(0)