查询以下的ratio, 语法是:
select json_array_elements(columnname->'Components'->'Component')#>>'{ratio}' as ratio from tablename;
其中,json_array_elements是把json数据立马数组部门取出。#>>是取数组里的数据
{
"NAVperCU": "2721431.78",
"CreationRedemptionUnit": "400000",
"Fundid1": "510501",
"MaxCashRatio": "0.50000",
"PreTradingDay": "20170328",
"Recordnum": "500",
"CashComponent": "-47401.88",
"Components": {
"Component": [
{
"ratio": "0.10000",
"sec": "深振业A",
"amount": " 5100.000",
"vol": " 600",
"code": "000006",
"mark": "3"
},
{
"ratio": "0.10000",
"sec": "南 玻A",
"amount": " 6756.000",
"vol": " 600",
"code": "000012",
"mark": "3"
]
}
}
得出结果是
ratio
-----------------------------
0.1
0.1