Search code examples
jsonparsingazure-sql-databasebulkopen-json

Parse JSON SQL with OPENJSON skipping OBJECTS

I'm trying to parse a json file using OPENJSON in Azure sql but I want to do a specific mapping in my new table. I have a json file which looks like this with many y0_1, y0_2, ....y0_380.

    {"proto": "01",
     "type": "A",

 "description": "heartbeat",
 "geometry": {"y0_1": {"tag": "Normal",
   "probability": 0.40,
   "x": 39,
   "y": 13},
  "y0_2": {"tag": "category_3", "probability": 0.8, "x": 48, "y": 13},
  "y0_3": {"tag": "Normal", "probability": 0.9, "x": 27, "y": 10},
"Test": {"proba": 0.65}}}

I want to parse the json and have a table With this format: I tried to parse the json using the Openjson format but I don't know how to get all the y and x in the same column like in the picture in the link

SELECT * from OPENROWSET (
        BULK 'sample.json',
        DATA_SOURCE ='dataset',
        FORMATFILE_DATA_SOURCE = 'myblobstorage',
        SINGLE_CLOB
        ) AS J
        CROSS APPLY OPENJSON (BulkColumn)
        WITH (
            proto nvarchar(100) N'$.proto',
            description nvarchar(100) N'$.description',
        
        )

table output desired

Solution

  • Use OPENJSON, the AS JSON data-type and CROSS APPLY to drill into different layers of JSON. I've created a simplified example to show the technique:

    DECLARE @json NVARCHAR(MAX) = '{
  "proto": "01",
  "type": "A",
  "description": "heartbeat",
  "geometry": {
    "y0_1": {
      "tag": "Normal",
      "probability": 0.40,
      "x": 39,
      "y": 13
    },
    "y0_2": {
      "tag": "category_3",
      "probability": 0.8,
      "x": 48,
      "y": 13
    },
    "y0_3": {
      "tag": "Normal",
      "probability": 0.9,
      "x": 27,
      "y": 10
    },
    "Test": {
      "proba": 0.65
    }
  }
}'


SELECT
    j.proto,
    j.[type],
    j.[description],
    JSON_VALUE ( g.[value], '$.tag' ) AS tag,
    JSON_VALUE ( g.[value], '$.probability' ) AS probability,
    JSON_VALUE ( g.[value], '$.x' ) AS x,
    JSON_VALUE ( g.[value], '$.y' ) AS y

FROM OPENJSON( @json, '$' )
WITH (
    proto VARCHAR(50),
    [type] VARCHAR(50),
    [description] VARCHAR(50),
    [geometry] NVARCHAR(MAX) AS JSON
    ) j
    CROSS APPLY OPENJSON ( [geometry] ) g

    My results:

    My results