Querying into arrays with JSON_VALUE

Question

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'

Zhorov · Accepted Answer · 2019-05-26 09:43:46Z

1

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

edited May 26, 2019 at 9:43

answered May 26, 2019 at 5:48

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying into arrays with JSON_VALUE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related