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?

Please edit your question to include the full and complete text of the error message you are getting. — AlwaysLearning, May 03 '21 at 07:58
Is this the actual JSON you're dealing with? SQL Server doesn't like JSON formatted with things such as `"Email": null,}` - try removing cleaning up the extra commas such as `"Email": null}`. — AlwaysLearning, May 03 '21 at 08:14
@AlwaysLearning Here is the error message: JSON text is not properly formatted. Unexpected character '1' is found at position 0. — Florentina, May 03 '21 at 08:19
I can't change it; we're getting data as a json file from other organisation. — Florentina, May 03 '21 at 08:22
I don't think anybody here can help you then. If the sender can only send sorta-JSON then SQL Server cannot sorta-parse it. You really need the sender to send properly formatted JSON data. — AlwaysLearning, May 03 '21 at 08:26

score 0 · Answer 1 · answered May 03 '21 at 08:38

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

I changed the SQL as suggested but I don't understand the first part of your answer 'create table.....' . There is already a table with this name which my SQL view has been created from. Can you please explain more how can I use/add this part to the table? should this be a part of the original table or..? — Florentina, May 03 '21 at 09:19
@Florentina The first part of the query is setting up a table and data for the select query consume. You don't need that since you have the schema and data in your database already. It's really there for other people that might want to answer this question - it should have been in the code presented in your question, ref: [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). — AlwaysLearning, May 03 '21 at 09:47

Extract values from an SQL column with JSON format

1 Answers1