I am trying to convert JSON into an SQL table. The source is the response from a REST API. I do not have the opportunity to change the response.
The response contains data on several projects. Projects are returned as individual members of a global projects object.
As the projectid is not at the same level with the other data and also does not have a name
SELECT * FROM OPENJSON (@JSON);
fails to parse it properly. I end up with one row for projects and the json object as the value. Also without the proper way to refer to the elements I could not figure out a way to use JSON_VALUE.
Below is a sample structure. Actual data has much more properties and variations between the projects. I prefer to solve this in SQL. But any way to sort out the JSON will be appreciated. This is my first exposure to JSON. I want to ask if I am missing something very obvious...
TIA
{
"projects": {
"project1id": {
"data": {
"customer": "Cust1",
"name": "Project Name 1"
},
"projectType": "type0"
},
"project2id": {
"data": {
"customer": "Customer 2",
"name": "Name 2",
"projectManager": "Man Ager"
},
"projectType": "type2"
},
"Project3id": {
"data": {
"customer": "Another Customer",
"name": "Another Project"
},
"projectType": "type1"
}
}
}
Expected Result
| ProjectId | Project Name | Customer | Project Type | Project Manager |
|---|---|---|---|---|
| project1id | Project Name 1 | Cust 1 | type0 | |
| project2id | Customer 2 | type2 | Man Ager | |
| project3id | Another Project | Another Customer | type1 |
Excerpt from original Json:
{
"projects": {
"10000eumbvqn76": {
"data": {
"inquiryNumber": "34635",
"customer": "C AS",
"name": "E W ",
"orderNumber": "1000",
"seller": "M A",
"projectManager": "B O V",
"phase": "fulfillment",
"exchange": {
"deadline": {
"time": [
24,
0
],
"timezone": [
1,
0
]
},
"settings": {
"client": {
"codes": [
{
"id": "1",
"label": "Code 1"
},
{
"id": "4",
"label": "Code 4"
},
{
"id": "5",
"label": "Code 5"
},
{
"id": "2",
"label": "Code 2"
},
{
"id": "3",
"label": "Code 3"
}
],
"approvedCodes": [
"1"
],
"cycles": {
"producer": 21,
"consumer": 21
}
},
"clientForInformation": {
"cycles": {
"producer": 21
}
},
"supplier": {
"codes": [
{
"id": "1",
"label": "Code 1"
},
{
"id": "4",
"label": "Code 4"
},
{
"id": "5",
"label": "Code 5"
},
{
"id": "2",
"label": "Code 2"
},
{
"id": "3",
"label": "Code 3"
}
],
"approvedCodes": [
"1"
],
"cycles": {
"producer": 14,
"consumer": 14
}
},
"supplierIsProducer": {
"supplierRole": "producer"
},
"supplierIsConsumer": {
"supplierRole": "consumer"
}
},
"sequences": {
"$salesPurchase": {
"label": "Client RFQ to OC",
"settings": "supplierIsProducer",
"group": "inquiry"
},
"$salesPurchaseSupplier": {
"label": "Supplier RFQ to OC",
"settings": "supplierIsConsumer",
"group": "inquiry"
},
"$salesClient": null,
"$salesClientFrom": null,
"$client": {
"label": "To client",
"group": "order-fulfillment",
"order": [
"IFR",
"IFI"
],
"stages": {
"IFR": {
"label": "Issued for Review",
"phase": "forApproval",
"settings": "client"
},
"IFI": {
"label": "Issued for Information",
"phase": "forInformation",
"settings": "clientForInformation"
}
},
"interpret": {
"type": "unordered"
}
},
"$supplier": {
"label": "From supplier",
"group": "order-fulfillment-supplier",
"settings": "supplier"
},
"$supplierTo": {
"label": "To supplier",
"group": "order-fulfillment-supplier",
"settings": "supplierIsConsumer"
},
"$internal": null
}
},
"officialMailIdFormat": "M-1000-0001",
"transmittalMailIdFormat": "TR-1000-0001",
"commercialMailIdFormat": "Bid-34635-0001",
"officialMailIdFormats": [
{
"label": "Official",
"format": "M-1000-0001"
},
{
"label": "Commercial",
"format": "Bid-34635-0001"
}
]
},
"projectType": "commercial"
},
"1000hf30ua": {
"data": {
"inquiryNumber": "100",
"customer": "S M I Y P L",
"name": "1000 FSPO ",
"seller": "L H",
"projectManager": "L H",
"phase": "inquiry",
"exchange": {
"deadline": {
"time": [
24,
0
],
"timezone": [
1,
0
]
},
"settings": {
"client": {
"codes": [
{
"id": "1",
"label": "Code 1"
},
{
"id": "4",
"label": "Code 4"
},
{
"id": "5",
"label": "Code 5"
},
{
"id": "2",
"label": "Code 2"
},
{
"id": "3",
"label": "Code 3"
}
],
"approvedCodes": [
"1"
],
"cycles": {
"producer": 21,
"consumer": 21
}
},
"clientForInformation": {
"cycles": {
"producer": 21
}
},
"supplier": {
"codes": [
{
"id": "1",
"label": "Code 1"
},
{
"id": "4",
"label": "Code 4"
},
{
"id": "5",
"label": "Code 5"
},
{
"id": "2",
"label": "Code 2"
},
{
"id": "3",
"label": "Code 3"
}
],
"approvedCodes": [
"1"
],
"cycles": {
"producer": 14,
"consumer": 14
}
},
"supplierIsProducer": {
"supplierRole": "producer"
},
"supplierIsConsumer": {
"supplierRole": "consumer"
}
},
"sequences": {
"$salesPurchase": {
"label": "Client RFQ to OC",
"settings": "supplierIsProducer",
"group": "inquiry"
},
"$salesPurchaseSupplier": {
"label": "Supplier RFQ to OC",
"settings": "supplierIsConsumer",
"group": "inquiry"
},
"$salesClient": null,
"$salesClientFrom": null,
"$client": {
"label": "To client",
"group": "order-fulfillment",
"order": [
"IFR",
"IFI"
],
"stages": {
"IFR": {
"label": "Issued for Review",
"phase": "forApproval",
"settings": "client"
},
"IFI": {
"label": "Issued for Information",
"phase": "forInformation",
"settings": "clientForInformation"
}
},
"interpret": {
"type": "unordered"
}
},
"$supplier": {
"label": "From supplier",
"group": "order-fulfillment-supplier",
"settings": "supplier"
},
"$supplierTo": {
"label": "To supplier",
"group": "order-fulfillment-supplier",
"settings": "supplierIsConsumer"
},
"$internal": null
}
},
"officialMailIdFormat": "M-100-0001",
"transmittalMailIdFormat": "TR-100-0001",
"commercialMailIdFormat": "Bid-100-0001",
"officialMailIdFormats": [
{
"label": "Official",
"format": "M-100-0001"
},
{
"label": "Commercial",
"format": "Bid-100-0001"
}
],
"orderNumber": "100"
},
"projectType": "commercial"
}
}
}
CodePudding user response：
Possible options are: 1) Using OPENJSON() twice (with default and explicit schema) and an additional APPLY operator or 2) Using OPENJSON() (with default schema) and JSON_VALUE():
JSON:
DECLARE @json nvarchar(max) = N'{
"projects":{
"project1id":{
"data":{
"customer":"Cust1",
"name":"Project Name 1"
},
"projectType":"type0"
},
"project2id":{
"data":{
"customer":"Customer 2",
"name":"Name 2",
"projectManager":"Man Ager"
},
"projectType":"type2"
},
"Project3id":{
"data":{
"customer":"Another Customer",
"name":"Another Project"
},
"projectType":"type1"
}
}
}'
Statement with OPENJSON() and APPLY operator:
SELECT j1.[key] AS projectId, j2.*
FROM OPENJSON(@json, '$.projects') j1
CROSS APPLY OPENJSON(j1.[value], '$') WITH (
name nvarchar(100) '$.data.name',
customer nvarchar(100) '$.data.customer',
projectType nvarchar(100) '$.projectType',
projectManager nvarchar(100) '$.data.projectManager'
) j2
Statement with OPENJSON() and JSON_VALUE():
SELECT
projectId = [key],
name = JSON_VALUE([value], '$.data.name'),
customer = JSON_VALUE([value], '$.data.customer'),
projectType = JSON_VALUE([value], '$.projectType'),
projectManager = JSON_VALUE([value], '$.data.projectManager')
FROM OPENJSON(@json, '$.projects')