PostgreSql数据库sql语句取Json值
1：json字段实例：
{
“boxNum”: 0,
“orderNum”: 0,
“commentNum”: 0
}
A.取boxNum的值
增加字段的sql语句1.1)select 字段名->‘boxNum’ from 表名;
1.2)select jsonb_extract_path_text字段名, ‘boxNum’) from 表名;
2：json字段实例：
{
“boxNum”: “0”,
“orderNum”: “0”,
“commentNum”: “0”
}
A.取boxNum的值，不带双引号。
2.1)select 字段名->>‘boxNum’ from 表名;
2.2)select jsonb_extract_path_text字段名, ‘boxNum’) from 表名;
3：json字段实例：
{
“unitPrices”: [{
“price”: 10.0,
“unitId”: “8”,
“unitName”: “500克”,
“unitAmount”: “0”,
“isPMDefault”: true,
“isHomeDefault”: true,
“originalPrice”: 10.0
}],
“productName”: “远洋 加拿⼤ 螯龙虾 野⽣捕捞”,
“productType”: 1,
“skuPortRate”: {
“id”: “a6b83048-3878-4698-88c2-2a9de288ac56”,
“cityId”: “2bf8c60c-789d-433a-91ae-8e4ae3e587a4”,
“dynamicProperties”: [{
“name”: “死亡率”,
“propertiesId”: “f05bda8c-f27c-4cc6-b97e-d4bd07272c81”,
“propertieValue”: {
“value”: “2.0”
}
}, {
“name”: “失⽔率”,
“propertiesId”: “ee9d95d7-7e28-4d54-b572-48ae64146c46”,
“propertieValue”: {
“value”: “3.0”
}
}]
},
“quotePriceAttribute”: {
“currencyName”: “⼈民币”
}
}
A.取quotePriceAttribute中的currencyName币制名称
select (字段名>>‘quotePriceAttribute’)::json->>‘currencyName’ from 表名；
B.取unitPrices中的price单价
select jsonb_array_elements((字段名->>‘unitPrices’)::jsonb)->>‘price’ from 表名；
C.取skuPortRate中的dynamicProperties的name为死亡率的propertieValue⾥⾯的value；
select bb->‘propertieValue’->>‘value’ as value from (
select jsonb_array_elements(((字段名->>‘skuPortRate’)::json->>‘dynamicProperties’)::jsonb) as bb from 表名) as dd where @> ‘{“name”: “死亡率”}’;
4.json字段实例：
[{“name”: “捕捞⽅式”, “showType”: 4, “propertiesId”: “9a14e435-9688-4e9b-b254-0e8e7cee5a65”,“propertieValue”: {“value”: “野⽣捕捞”, “enValue”: “Wild”}},
{“name”: “加⼯⽅式”, “showType”: 4, “propertiesId”: “7dc101df-d262-4a75-bdca-9ef3155b7507”,“propertieValue”: {“value”: “单冻”, “enValue”: “Individual Quick Freezing”}},
{“name”: “原产地”, “showType”: 4, “propertiesId”: “dc2b506e-6620-4e83-8ca1-a49fa5c5077a”,“propertieValue”: {“value”: “爱尔兰”, “remark”: “”, “enValue”: “Ireland”}}]
–获取原产地
select
(SELECT ss->‘propertieValue’ as mm FROM
(SELECT jsonb_array_elements (dynamic_properties) AS ss FROM product
where ) as dd where dd.ss @> ‘{“name”: “原产地”}’)->>‘value’ as cuntry,
a.*
from product as a where a.id=‘633dd80f-7250-465f-8982-7a7f01aaeeec’；
5：json例⼦:huren:[“aaa”,“bbb”,“ccc”…]
需求：取值aaa去““双引号”
select replace(cast(jsonb_array_elements(huren) as text), ‘"’,’’) from XXX limit 1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。