declare @json as nvarchar(max)
set @json=N'
{
"code": 0,
"msg": null,
"data": {
"records": [
{
"orderId": "XS202305261022265859370",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 2,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1231231230",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-15 01:42:48",
"orderFormDetail": [
{
"id": "54",
"orderId": "XS202305261022265859370",
"cuisineId": "107",
"cuisineName": "里脊",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "55",
"orderId": "XS202305261022265859370",
"cuisineId": "120",
"cuisineName": "豆腐",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202305261747406900030",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 2,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:36",
"orderFormDetail": [
{
"id": "56",
"orderId": "XS202305261747406900030",
"cuisineId": "101",
"cuisineName": "清蒸鱼",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "57",
"orderId": "XS202305261747406900030",
"cuisineId": "108",
"cuisineName": "菠萝肉",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202305272222357044510",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 3,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237220",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-13 09:36:03",
"orderFormDetail": [
{
"id": "58",
"orderId": "XS202305272222357044510",
"cuisineId": "120",
"cuisineName": "红烧思磊",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "59",
"orderId": "XS202305272222357044510",
"cuisineId": "120",
"cuisineName": "鱼香晨涛",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202305272225219575940",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 4,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:36",
"orderFormDetail": [
{
"id": "60",
"orderId": "XS202305272225219575940",
"cuisineId": "120",
"cuisineName": "鱼香肉丝",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "61",
"orderId": "XS202305272225219575940",
"cuisineId": "120",
"cuisineName": "红烧茄子",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202305272232571429020",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 5,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318123456789",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:36",
"orderFormDetail": [
{
"id": "62",
"orderId": "XS202305272232571429020",
"cuisineId": "120",
"cuisineName": "鱼香丝",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "63",
"orderId": "XS202305272232571429020",
"cuisineId": "120",
"cuisineName": "红烧茄",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202306021514358209560",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 6,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:36",
"orderFormDetail": [
{
"id": "64",
"orderId": "XS202306021514358209560",
"cuisineId": "120",
"cuisineName": "鱼香肉丝",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "65",
"orderId": "XS202306021514358209560",
"cuisineId": "120",
"cuisineName": "红烧茄子",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202306021524555539610",
"storeId": "0001",
"paymentId": "1",
"memberId": "5",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 5,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:36",
"orderFormDetail": [
{
"id": "66",
"orderId": "XS202306021524555539610",
"cuisineId": "120",
"cuisineName": "鱼香肉丝",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "67",
"orderId": "XS202306021524555539610",
"cuisineId": "120",
"cuisineName": "红烧茄子",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:49",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202306071105551957060",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 2,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:37",
"orderFormDetail": [
{
"id": "94",
"orderId": "XS202306071105551957060",
"cuisineId": "120",
"cuisineName": "鱼香肉丝",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:51",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "95",
"orderId": "XS202306071105551957060",
"cuisineId": "120",
"cuisineName": "红烧茄子",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:51",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202306071110258589930",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 5,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:37",
"orderFormDetail": [
{
"id": "96",
"orderId": "XS202306071110258589930",
"cuisineId": "120",
"cuisineName": "鱼香肉丝",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:51",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "97",
"orderId": "XS202306071110258589930",
"cuisineId": "120",
"cuisineName": "红烧茄子",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:51",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
},
{
"orderId": "XS202306071127416209000",
"storeId": "0001",
"paymentId": "1",
"memberId": "6",
"groupId": null,
"isGroupId": null,
"couponCode": null,
"orderAmount": 89.12,
"payAmount": 89.12,
"paySource": 1,
"orderStatusList": null,
"orderStatus": 5,
"integrationDeductions": null,
"orderTime": "2023-05-18 14:45:10",
"orderFinishTime": "2023-05-09 14:50:06",
"productionTime": "2023-05-09 14:50:06",
"discount": 89.12,
"takeMealsName": "jack_wang",
"takeMealsPhone": "1318237",
"tipRatio": 89.12,
"takeMealsType": 0,
"createdBy": "6",
"createdTime": "2023-05-18 00:00:00",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:37",
"orderFormDetail": [
{
"id": "100",
"orderId": "XS202306071127416209000",
"cuisineId": "120",
"cuisineName": "鱼香肉丝",
"size": "2",
"num": 1,
"favourablePrice": 99.99,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:51",
"cuisineType": 0,
"price": 99,
"skusPrice": null
},
{
"id": "101",
"orderId": "XS202306071127416209000",
"cuisineId": "120",
"cuisineName": "红烧茄子",
"size": "2",
"num": 1,
"favourablePrice": 99.97,
"createdBy": "6",
"createdTime": "2023-05-18 14:45:10",
"updatedBy": null,
"updatedTime": "2023-06-12 01:59:51",
"cuisineType": 0,
"price": 99,
"skusPrice": null
}
],
"startDate": null,
"stopDate": null
}
],
"total": 74,
"size": 10,
"current": 1,
"orders": [],
"optimizeCountSql": true,
"searchCount": true,
"maxLimit": null,
"countId": null,
"pages": 8
}
}
'
--1,解析json外层数据
select * from openjson(@json,'$.data.records')
with (
orderId nvarchar(500) ,
storeId nvarchar(100) ,
paymentId nvarchar(100) ,
memberId nvarchar(100) ,
groupId nvarchar(100) ,
isGroupId nvarchar(100) ,
couponCode nvarchar(100) ,
orderAmount nvarchar(100) ,
payAmount nvarchar(100) ,
paySource nvarchar(100) ,
orderStatusList nvarchar(100) ,
orderStatus nvarchar(100) ,
integrationDeductions nvarchar(100) ,
orderTime nvarchar(100) ,
orderFinishTime nvarchar(100) ,
productionTime nvarchar(100) ,
discount nvarchar(100) ,
takeMealsName nvarchar(100) ,
takeMealsPhone nvarchar(100) ,
tipRatio nvarchar(100) ,
takeMealsType nvarchar(100) ,
createdBy nvarchar(100) ,
createdTime nvarchar(100) ,
updatedBy nvarchar(100) ,
updatedTime nvarchar(100)
)
--2,解析json内层数据
select b.*
from openjson(@json,'$.data.records')
WITH ( orderFormDetail NVARCHAR(MAX) AS JSON ) AS a
CROSS APPLY OPENJSON(a.orderFormDetail)
WITH ( id NVARCHAR(MAX) ,
orderId NVARCHAR(MAX) ,
cuisineId NVARCHAR(MAX) ,
cuisineName NVARCHAR(MAX) ,
size NVARCHAR(MAX) ,
num NVARCHAR(MAX) ,
favourablePrice NVARCHAR(MAX) ,
createdBy NVARCHAR(MAX) ,
createdTime NVARCHAR(MAX) ,
updatedBy NVARCHAR(MAX) ,
updatedTime NVARCHAR(MAX) ,
cuisineType NVARCHAR(MAX) ,
price NVARCHAR(MAX) ,
skusPrice NVARCHAR(MAX)
) as b
MSSQL解析多层嵌套json文件
最新推荐文章于 2024-05-28 11:19:56 发布