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"},
]
1
2
3
4
5

• 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56