CODEWITHSUNDEEP
jsonsql-server
Chris Auer
Chris Auer

Reputation: 1435

Querying into arrays with JSON_VALUE

I have a field with the following JSON data in it

[
   {
      "@odata.type":"#Sitecore.XConnect.Goal",
      "CustomValues":[

      ],
      "DefinitionId":"82c4c49c-b6b2-4b02-8e2f-fbcba9f92fe4",
      "EngagementValue":60,
      "Id":"335c92ce-5e36-4b13-9472-4940ad66e75f",
      "Timestamp":"2019-05-07T23:53:34.4268677Z"
   }
]

I am trying to find all SQL rows that have the field @odata.type equal to #Sitecore.XConnect.Goal. But it is inside of an array. I have tried the SQL below, but I can't return any SQL rows.

SELECT *
  FROM [client_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
  WHERE JSON_VALUE([Events], '$.[0]"odata.type"') = '#Sitecore.XConnect.Goal'

Upvotes: 0

Views: 170

Answers (1)

Zhorov
Zhorov

Reputation: 29943

You need to specify the path as '$[0]."@odata.type"' (missing dot operator . that indicates a member of your $[0] object).

SELECT *
FROM [usms_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
WHERE JSON_VALUE([Events], '$[0]."@odata.type"') = '#Sitecore.XConnect.Goal'

Example:

DECLARE @json nvarchar(max) = N'[
   {
      "@odata.type":"#Sitecore.XConnect.Goal",
      "CustomValues":[

      ],
      "DefinitionId":"82c4c49c-b6b2-4b02-8e2f-fbcba9f92fe4",
      "EngagementValue":60,
      "Id":"335c92ce-5e36-4b13-9472-4940ad66e75f",
      "Timestamp":"2019-05-07T23:53:34.4268677Z"
   }
]'

SELECT JSON_VALUE(@json, '$[0]."@odata.type"') AS JsonValue

Output:

----------------------- 
JsonValue
----------------------- 
#Sitecore.XConnect.Goal

Upvotes: 1

Related Questions