Extract values from an SQL column with JSON format

Question

I need to extract values from a column which has a JSON format. Here is an example of one of rows in table for this column, here is two operatorKeys but in other rows there might be more:

{
"officeId": "office8685",
"TypeOfOffice": null,
"Telefonnummer": "",
"Operatorer": [
    {
        "OperatorKey": "1",
        "OperatorUserID": {
            "Agency": "Other",
            "AgencySpecified": true,
            "Value": "TM-Oper-1"
        },
        "ContactInformation": {
            "FirstName": "mike",
            "LastName": null,
            "Address": {
                "Street": null,
                "City": null,
                "Province": null,
                "Country": null
            },
            "Phone": null,
            "Fax": null,
            "Email": null, 
        }
    },
    {
        "OperatorKey": "4",
        "OperatorUserID": {
            "Agency": "Other",
            "AgencySpecified": true,
            "Value": "TM-Oper-4"
        },
        "ContactInformation": {
            "FirstName": "xxx",
            "LastName": null,
            "Address": {
                "Street": null,
                "City": null,
                "Province": null,
                "Country": null
            },
            "Phone": null,
            "Fax": null,
            "Email": null,
        }
    }       
]

}

And here is SQL view I'm trying to read JSON, I'm using openjson and cross apply to read values:

Alter view  dbo.vOffice as 
select
      column1,column2
      --,column3 --this column contains values with json format
      ,OfficId,TypeOfOffice,Telefonnummer,Operatorer,OperatorKey,OperatorUserID                          
      ,ContactInformation,Agency,AgencySpecified,[Value],FirstName,LastName                              
      ,[Address],Phone, Fax, Email 
FROM [produktion_dim].[VirkesverdeStandardSCD1]
CROSS APPLY OPENJSON (column3,'$.OfficId') with (
OfficId varchar(4096)   '$.OfficId',TypeOfOffice varchar(4096)  '$.TypeOfOffice',
Telefonnummer varchar(4096) '$.Telefonnummer',Operatorer varchar(4096)  '$.Operatorer')
cross apply openjson(Operatorer, '$')  with(OperatorKey  varchar(4096)  '$.OperatorKey', OperatorUserID varchar(4096) '$.OperatorUserID', ContactInformation     varchar(4096)  '$.ContactInformation')
cross apply openjson(OperatorUserID,'$') with ( Agency varchar(4096) '$.Agency',
AgencySpecified varchar(4096) '$.AgencySpecified',[Value]                           varchar(4096)   '$.Value'   )
cross apply openjson( ContactInformation, '$') with (FirstName                              varchar(4096)   '$.FirstName',LastName varchar(4096)    '$.LastName',[Address] varchar(4096)    '$.Address',Phone   varchar(4096)   '$.Phone',Fax       varchar(4096)   '$.Fax',Email   varchar(4096)   '$.Email')
cross apply openjson([Address],'$') with ( Street varchar(4096) '$.Street',
City varchar(4096)  '$.City',Province varchar(4096) '$.Province',Country    varchar(4096) '$.Country')

But I'm getting error. Can you please tell me what is missing or what should be changed in SQL view?

Answer 1

Once you can organize the sender to send correctly formatted JSON data here are some tips to help you parse the JSON data:

• The '$' in openjson(something, '$') with (...) is redundant, just use openjson(something) with (...)
• JSON path queries are case sensitive: $.officeId is different than $. OfficeId and neither would match '$.OfficId'.
• inside with (...) when declaring a column to be consumed by another openjson() call it needs to use the type nvarchar(max) and include as json in its definition, e.g.: OperatorUserID nvarchar(max) '$.OperatorUserID' as json.

I have edited your query to extract the JSON data I think you're looking for...

create table dbo.VirkesverdeStandardSCD1 (
  column3 nvarchar(max)   
);

insert dbo.VirkesverdeStandardSCD1 (column3) values (N'{
    "officeId": "office8685",
    "TypeOfOffice": null,
    "Telefonnummer": "",
    "Operatorer": [
        {
            "OperatorKey": "1",
            "OperatorUserID": {
                "Agency": "Other",
                "AgencySpecified": true,
                "Value": "TM-Oper-1"
            },
            "ContactInformation": {
                "FirstName": "mike",
                "LastName": null,
                "Address": {
                    "Street": null,
                    "City": null,
                    "Province": null,
                    "Country": null
                },
                "Phone": null,
                "Fax": null,
                "Email": null
            }
        },
        {
            "OperatorKey": "4",
            "OperatorUserID": {
                "Agency": "Other",
                "AgencySpecified": true,
                "Value": "TM-Oper-4"
            },
            "ContactInformation": {
                "FirstName": "xxx",
                "LastName": null,
                "Address": {
                    "Street": null,
                    "City": null,
                    "Province": null,
                    "Country": null
                },
                "Phone": null,
                "Fax": null,
                "Email": null
            }
        }
    ]
}');

select
      --column1,column2
      --,column3 --this column contains values with json format
      OfficId,TypeOfOffice,Telefonnummer,Operatorer,OperatorKey,OperatorUserID                          
      ,ContactInformation,Agency,AgencySpecified,[Value],FirstName,LastName                              
      ,[Address],Phone, Fax, Email 
from dbo.VirkesverdeStandardSCD1
cross apply openjson(column3) with (
  OfficId varchar(4096)   '$.officeId',
  TypeOfOffice varchar(4096)  '$.TypeOfOffice',
  Telefonnummer varchar(4096) '$.Telefonnummer',
  Operatorer nvarchar(max) '$.Operatorer' as json
)
cross apply openjson(Operatorer) with (
  OperatorKey varchar(4096) '$.OperatorKey',
  OperatorUserID nvarchar(max) '$.OperatorUserID' as json,
  ContactInformation nvarchar(max) '$.ContactInformation' as json
)
cross apply openjson(OperatorUserID) with (
  Agency varchar(4096) '$.Agency',
  AgencySpecified varchar(4096) '$.AgencySpecified',
  [Value] varchar(4096) '$.Value'
)
cross apply openjson(ContactInformation) with (
  FirstName varchar(4096) '$.FirstName',
  LastName varchar(4096) '$.LastName',
  [Address] nvarchar(max) '$.Address' as json,
  Phone varchar(4096) '$.Phone',
  Fax varchar(4096) '$.Fax',
  Email varchar(4096) '$.Email'
)
cross apply openjson([Address]) with (
  Street varchar(4096) '$.Street',
  City varchar(4096) '$.City',
  Province varchar(4096) '$.Province',
  Country varchar(4096) '$.Country'
);