SQL Express 2016 Parse JSON string

Are there any tutorials on parsing custom entity data via sql? I’m using SQL express 2016 and I read that this capability has been added, I just can’t figure out how to get it to work.
I can’t figure out how to specify what single value I require because the keys are not unique.
I have tried using
SELECT JSON_VALUE(EntityCustomData,'$.Name') and SELECT JSON_QUERY(EntityCustomData,'$.Name')

I’m trying to get the customer name out of the following string.

[{"Name":"Type","Value":"Standard"},{"Name":"Customer","Value":"Cash Sales"},{"Name":"Address","Value":null},{"Name":"Email","Value":null},{"Name":"Company Name","Value":null},{"Name":"Other Phone","Value":null}]

SELECT
e.[Id]
--,e.[EntityTypeId]
,et.[Name] as [EntityType]
,e.[Name]
,jsonData.[cdName]
,jsonData.[cdValue]
--,e.[CustomData]
--,e.[Notes]
--,e.[AccountId]
--,e.[WarehouseId]
--,e.[LastUpdateTime]
--,e.[SearchString]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]

CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/fields above in the SELECT portion
-- we can also use these as filters in the WHERE clause below
WITH (   
 cdName         varchar(1000) '$.Name'
,cdValue        varchar(1000) '$.Value'
) jsonData

WHERE 1=1
-- filter for specific Entity Type
AND et.[Name]='Customers'
-- filter for specific Custom Data Field Name
AND jsonData.[cdName]='Phone'

ORDER BY et.[Name],e.[Name],jsonData.[cdName]

##Results

2 Likes

I’m trying to extract 2 fields but I don’t think this is the correct approach.

SELECT
e.[Id]
--,e.[EntityTypeId]
,et.[Name] as [EntityType]
,e.[Name]
,jsonData.[cdName]
,jsonData.[cdValue]
,jsonData.[emName]
,jsonData.[emValue]
--,e.[CustomData]
--,e.[Notes]
--,e.[AccountId]
--,e.[WarehouseId]
--,e.[LastUpdateTime]
--,e.[SearchString]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]

CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/fields above in the SELECT portion
-- we can also use these as filters in the WHERE clause below
WITH (   
 cdName         varchar(1000) '$.Name'
,cdValue        varchar(1000) '$.Value'
,emName         varchar(1000) '$.Name'
,emValue        varchar(1000) '$.Value'
) jsonData

WHERE 1=1
-- filter for specific Entity Type
AND et.[Name]='Customers'
-- filter for specific Custom Data Field Name
AND jsonData.[cdName]='Address' AND jsonData.[emName]='Email'

ORDER BY et.[Name],e.[Name],jsonData.[cdName]

jsonData.[cdName] and jsonData.[emName] both refer to the same key ($.Name) so this:

AND jsonData.[cdName]='Address' AND jsonData.[emName]='Email'

… is like saying this:

$.Name='Address' AND $.Name='Email'

… so you can see how that will never be TRUE, so you won’t get any results.

Make that an OR and you will get records. However, you will get 2 records per Customer. I see that you probably want 1 record per Customer, so you want to PIVOT the data. There are different ways to do that, but a simple method in this case is to SELECT the data twice and use a UNION and then GROUP BY to collapse the data.

SELECT
 [Id]
,[EntityType]
,[Name]
--,max([cdName]) as [cdName]
,max([cdValue]) as [Address]
--,max([emName]) as [emName]
,max([emValue]) as [Email]
FROM (

-- get the Address Field
SELECT
e.[Id]
,et.[Name] as [EntityType]
,e.[Name]
,jsonData.[cdName] as [cdName]
,jsonData.[cdValue] as [cdValue]
,'' as [emName]
,'' as [emValue]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]
CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/fields above in the SELECT portion
-- we can also use these as filters in the WHERE clause below
WITH (   
 cdName         varchar(1000) '$.Name'
,cdValue        varchar(1000) '$.Value'
,emName         varchar(1000) '$.Name'
,emValue        varchar(1000) '$.Value'
) jsonData

WHERE 1=1
-- filter for specific Entity Type
AND et.[Name]='Customers'
-- filter for specific Custom Data Field Name
AND jsonData.[cdName]='Address'


UNION


-- get the Email Field
SELECT
e.[Id]
,et.[Name] as [EntityType]
,e.[Name]
,'' as [cdName]
,'' as [cdValue]
,jsonData.[emName] as [emName]
,jsonData.[emValue] as [emValue]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]
CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/fields above in the SELECT portion
-- we can also use these as filters in the WHERE clause below
WITH (   
 cdName         varchar(1000) '$.Name'
,cdValue        varchar(1000) '$.Value'
,emName         varchar(1000) '$.Name'
,emValue        varchar(1000) '$.Value'
) jsonData

WHERE 1=1
-- filter for specific Entity Type
AND et.[Name]='Customers'
-- filter for specific Custom Data Field Name
AND jsonData.[emName]='Email'

) u

GROUP BY  [Id],[EntityType],[Name]

ORDER BY [EntityType],[Name]
3 Likes

Thanks for your help. That’s a bit shorter than what i ended up doing.
I declared 2 temporary tables and then pulled the data from those.

You see Q you do know everything! “Cross Apply” do not see that every day! :grinning:

I am having an issue where an order tags contains the . character

Here is an example string, the Discount ordertag note i think TO and PR (tag price?) is -9.99 so I get the following error

Msg 13609, Level 16, State 4, Line 2
JSON text is not properly formatted. Unexpected character '.' is found at position 0.

[{"OI":9,"OK":"000010","PR":-9.99,"Q":1,"TN":"Discount Custom","TO":"-9.99","TV":"***DISCOUNTED","UI":1}]

The TO value is probably fine because it is quoted, but the PR (price) value is not.

Where/how are you seeing this error?

I have parsed Order Tags before using SQL JSON functions and I don’t remember seeing this error.

EDIT: I see the error now in the following query; strange that I did not notice this before. Maybe because my Tag Prices never use to contain decimals.

SELECT
 [Id]
,[TicketId]
,[MenuItemName]
,[PortionName]
,[Price]
,[Quantity]
,tagName
,tagValue
,tagPrice
,tagNote
--,[OrderTags]
,[OrderStates]
,[MenuItemId]
--,[PortionCount]
--,[Locked]
--,[CalculatePrice]
--,[DecreaseInventory]
--,[IncreaseInventory]
--,[OrderNumber]
--,[CreatingUserName]
--,[CreatedDateTime]
--,[LastUpdateDateTime]
--,[AccountTransactionTypeId]
--,[ProductTimerValueId]
--,[GroupTagName]
--,[GroupTagFormat]
--,[Separator]
--,[PriceTag]
--,[Tag]
--,[DisablePortionSelection]
--,[OrderUid]
--,[Taxes]
--,[WarehouseId]
--,[DepartmentId]
--,[TerminalId]
FROM [Orders] o
CROSS APPLY OPENJSON(o.[OrderTags])
WITH (
 tagName         varchar(1000) '$.TN'
,tagValue        varchar(1000) '$.TV'
,tagPrice        varchar(1000) '$.PR'
,tagNote         varchar(1000) '$.TO'
) jd
WHERE 1=1

EDIT2: Hmm strange. The first record it seems to be complaining about does not even have any data in the [OrderTags] column (blank). If I add this constraint to the WHERE clause, no error is produced:

AND [OrderTags]!=''

That seems to have done the trick. The error is a bit misleading,

I can just use a union then to add any orders without tags.