0

I'm trying to return a JSON_QUERY field only if the JSON contains a specific field/property, but when I compare it with a string (that is the field's name), the function doesn't returns a JSON...

Single code without comparison:

SELECT Field1, Field2
       ,JSON_QUERY('[' + json.CrossApplyQuery + ']', '$') AS [Returns_JSON]

Return:

Field1   |  Field2  |  Returns_JSON
Value1   |  Value2  |  [{"SomeQueryField":"SomeStringValue"]}]

Code with comparisons using JSON:

SELECT Field1, Field2
       ,CASE WHEN CHARINDEX('_JsonProperty', UPPER(json.CrossApplyQuery)) > 0 THEN JSON_QUERY('[' + json.CrossApplyQuery + ']', '$') ELSE NULL END AS [Returns_String]
       ,CASE WHEN Exists(Select * FROM OPENJSON(JSON_QUERY('[' + json.CrossApplyQuery + ']', '$'),'$') WHERE value like '%_JsonProperty%') THEN JSON_QUERY('[' + json.CrossApplyQuery + ']', '$') ELSE NULL END AS [Returns_String_Too]

Return:

Field1   |  Field2  |  Returns_String                               |  Returns_String_Too
Value1   |  Value2  |  "[{\"SomeQueryField\":\"SomeStringValue\"}]  |  "[{\"SomeQueryField\":\"SomeStringValue\"}]

If I doesn't use the JSON field to compare (any other condition), it works!

SELECT Field1, Field2
       ,CASE WHEN 0 < 1 THEN JSON_QUERY('[' + json.CrossApplyQuery + ']', '$') ELSE NULL END AS [Returns_JSON]

Return:

Field1   |  Field2  |  Returns_JSON
Value1   |  Value2  |  [{"SomeQueryField":"SomeStringValue"]}]

Question: How do I check if the JSON contains the field/property without changing it to string?

Fernando Moreno
  • 41
  • 5
  • JSON *is* a string; normally an `nvarchar(MAX)`. What you mean by how do you read it without turning it into one? Are you actually storing it in a `varbinary` or something? – Thom A Nov 06 '20 at 14:17
  • I know Json is essentially a string... but if you look the results you will see the differences when I use the comparison and when not... this one single query... not storing it, just return... The thing is the return changes when I compare the json object with some string... – Fernando Moreno Nov 06 '20 at 15:00

1 Answers1

0

Issue solved using this way:

JSON_QUERY('[' + CASE WHEN CHARINDEX('_JsonProperty', UPPER(json.CrossApplyQuery)) > 0 THEN json.CrossApplyQuery ELSE NULL END + ']', '$') [Returns_JSON]

Don't know why, but it worked... Tks!

Fernando Moreno
  • 41
  • 5