How to parse a JSON with missing property name into a db table

Question

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"
      }
    }
}

What you your expected results here? What was your actual attempt, as just OPENJSON (@JSON) isn't going to return much more than a few rows containing more JSON, as your JSON is nested. — Thom A
– Thom A ♦, Commented Feb 14, 2022 at 12:23
Why projectType is inside the $.data part of the 3rd JSON object - is it a typing error? — Zhorov
– Zhorov, Commented Feb 14, 2022 at 12:29
Added the expected result table. And corrected the JSON typing error. — rziya
– rziya, Commented Feb 14, 2022 at 12:44

Zhorov · Accepted Answer · 2022-02-14 13:20:53Z

1

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')

edited Feb 14, 2022 at 13:20

answered Feb 14, 2022 at 12:43

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rziya Over a year ago

Both return 0 rows for me...

Zhorov Over a year ago

@rziya, what is the actual JSON content? Note, that path definitions ($.projects) are case-sensitive and this may explain the 0 rows results.

Anton Grig Over a year ago

It should work with this JSON sample.

rziya Over a year ago

This does not work at my end. Both queries return 0 rows. I also added the original JSON.

rziya Over a year ago

Thanks. I now see the problem...

Collectives™ on Stack Overflow

How to parse a JSON with missing property name into a db table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related