如何通过CROSS APPLY获取多层嵌套JSON属性的值？

huangapple
go评论120阅读模式
英文:

How to get a multi-level Nested JSON property's value via CROSS APPLY?

问题

以下是翻译好的部分：

  1. {
  2.     "id": "f53283cc-5af7-4864-a3f6-86f69d81ae93",
  3.     "SectionId": 5493,
  4.     "Fields": [
  5.         {
  6.             "FieldId": 17943,
  7.             "FieldValue": "[{\"Web_Id\":\"1;#99999984_6_11_018111\",\"EDF\":[{\"key\":\"Orders\",\"val\":\"1\"}],\"CCID\":\"1;#AVSPN004987\",\"G\":null}]",
  8.             "FieldName": "<p>Primary_Product</p>17943",
  9.             "VersionID": 2
  10.         }
  11.     ]
  12. }

请注意，我已将HTML实体编码解码为正常的JSON文本。如果您需要进一步的帮助，请告诉我。

英文:

We have JSON data in this format:

  1. {
  2.     &quot;id&quot;: &quot;f53283cc-5af7-4864-a3f6-86f69d81ae93&quot;,
  3.     &quot;SectionId&quot;: 5493,
  4.     &quot;Fields&quot;: [
  5.         {
  6.             &quot;FieldId&quot;: 17943,
  7.             &quot;FieldValue&quot;: 
  8.                 &quot;[
  9.                     {
  10.                         \&quot;Web_Id\&quot;:\&quot;1;#99999984_6_11_018111\&quot;,
  11.                         \&quot;EDF\&quot;:
  12.                             [
  13.                                 {
  14.                                     \&quot;key\&quot;:\&quot;Orders\&quot;,
  15.                                     \&quot;val\&quot;:\&quot;1\&quot;
  16.                                 }
  17.                             ],
  18.                         \&quot;CCID\&quot;:\&quot;1;#AVSPN004987\&quot;,
  19.                         \&quot;G\&quot;:null
  20.                     }
  21.                 ]&quot;,
  22.             &quot;FieldName&quot;: &quot;&lt;p&gt;Primary_Product&lt;/p&gt;17943&quot;,
  23.             &quot;VersionID&quot;: 2
  24.         }
  25.     ]
  26. }

From this data, I can pull values from the Fields property level like the FieldID, FieldValue, FieldName and VersionID using the query below. However, I would like to know if it's possible to pull the WebId or the CCID from the FieldValue element? Currently, I'm doing a SUBSTRING to get the value:

  1. SELECT
  2.   AC_ID
  3.   , FieldID
  4.   , CASE
  5.     WHEN FieldName LIKE &#39;%Item_Description%&#39; THEN &#39;Item Description&#39;
  6.     WHEN FieldName LIKE &#39;%Item_Number%&#39; THEN &#39;Item Number&#39;
  7.     WHEN FieldName LIKE &#39;%Primary_Product%&#39; THEN &#39;Primary Product&#39;
  8.     ELSE &#39;&#39;
  9.     END AS Field
  10.   , CASE
  11.     WHEN FieldID &lt;&gt; &#39;17943&#39; THEN FieldValue
  12.     WHEN FieldID = &#39;17943&#39; THEN SUBSTRING(STUFF(FieldValue, 1, 15, &#39;&#39;), 1, CHARINDEX(&#39;&quot;&#39;, STUFF(FieldValue, 1, 15, &#39;&#39;)) - 1)
  13.     ELSE NULL
  14.     END AS FieldValue
  15.   , SUBSTRING(STUFF(FieldValue, 1, 15, &#39;&#39;), 1, CHARINDEX(&#39;&quot;&#39;, STUFF(FieldValue, 1, 15, &#39;&#39;)) - 1)
  17. FROM CTE_BASE
  18. CROSS APPLY OPENJSON(CTE_BASE.Fields)
  19. WITH (
  20.   FieldId VARCHAR(10) &#39;$.FieldId&#39;
  21.   , FieldName VARCHAR(MAX) &#39;$.FieldName&#39;
  22.   , FieldValue VARCHAR(MAX) &#39;$.FieldValue&#39;
  23. ) AS CDB

答案1

得分: 4

以下是您要翻译的内容：

"$.FieldValue" 部分的 JSON 似乎是存储为字符串的 JSON 数组。但我认为使用一个额外的 "APPLY" 运算符将返回期望的结果。

简化的测试数据：

  1. DECLARE @json nvarchar(max) = N'{
  2.    "id":"f53283cc-5af7-4864-a3f6-86f69d81ae93",
  3.    "SectionId":5493,
  4.    "Fields":[
  5.       {
  6.          "FieldId":17943,
  7.          "FieldValue":"[{"Web_Id":"1;#99999984_6_11_018111","EDF":[{"key":"Orders","val":"1"}],"CCID":"1;#AVSPN004987","G":null}]",
  8.          "FieldName":"<p>Primary_Product</p>17943",
  9.          "VersionID":2
  10.       }
  11.    ]
  12. }';

语句：

  1. SELECT *
  2. FROM OPENJSON(@json, '$.Fields') WITH (
  3.   FieldId VARCHAR(10) '$.FieldId', 
  4.   FieldName VARCHAR(MAX) '$.FieldName', 
  5.   FieldValue NVARCHAR(MAX) '$.FieldValue'
  6. ) j1
  7. CROSS APPLY OPENJSON(j1.FieldValue) WITH (
  8.   Web_Id varchar(50) '$.Web_Id',
  9.   CCID varchar(50) '$.CCID'
  10. ) j2
FieldId FieldName FieldValue Web_Id CCID
17943

Primary_Product

17943

 [{"Web_Id":"1;#99999984_6_11_018111","EDF":[{"key":"Orders","val":"1"}],"CCID":"1;#AVSPN004987","G":null}] 1;#99999984_6_11_018111 1;#AVSPN004987

请注意，如果存储的 JSON 中的 "Field.Value" 部分存储为 JSON 数组，而不是字符串，您将需要使用 "AS JSON" 修饰符来获取嵌套的 JSON 内容。

作为文本的 JSON 数组：

  1. DECLARE @json1 nvarchar(max) = N'{
  2.    "Fields":[
  3.       {"FieldValue":"[{"key":"value"}]"}
  4.    ]
  5. }';
  6. SELECT *
  7. FROM OPENJSON(@json1, '$.Fields') WITH (
  8.   FieldValue NVARCHAR(MAX) '$.FieldValue'
  9. ) j1
  10. CROSS APPLY OPENJSON(j1.FieldValue) WITH (
  11.   [key] varchar(50) '$.key'
  12. ) j2

作为 JSON 的 JSON 数组：

  1. DECLARE @json2 nvarchar(max) = N'{
  2.    "Fields":[
  3.       {"FieldValue":[{"key":"value"}]}
  4.    ]
  5. }';
  6. SELECT *
  7. FROM OPENJSON(@json2, '$.Fields') WITH (
  8.   FieldValue NVARCHAR(MAX) '$.FieldValue' AS JSON
  9. ) j1
  10. CROSS APPLY OPENJSON(j1.FieldValue) WITH (
  11.   [key] varchar(50) '$.key'
  12. ) j2
FieldValue key
[{"key":"value"}] value
英文:

It seems that the $.FieldValue part of the JSON is a JSON array stored as a string. But I think that one addiitonal APPLY operator will return the expected results.

Simplified test data:

  1. DECLARE @json nvarchar(max) = N&#39;{
  2.    &quot;id&quot;:&quot;f53283cc-5af7-4864-a3f6-86f69d81ae93&quot;,
  3.    &quot;SectionId&quot;:5493,
  4.    &quot;Fields&quot;:[
  5.       {
  6.          &quot;FieldId&quot;:17943,
  7.          &quot;FieldValue&quot;:&quot;[{\&quot;Web_Id\&quot;:\&quot;1;#99999984_6_11_018111\&quot;,\&quot;EDF\&quot;:[{\&quot;key\&quot;:\&quot;Orders\&quot;,\&quot;val\&quot;:\&quot;1\&quot;}],\&quot;CCID\&quot;:\&quot;1;#AVSPN004987\&quot;,\&quot;G\&quot;:null}]&quot;,
  8.          &quot;FieldName&quot;:&quot;&lt;p&gt;Primary_Product&lt;/p&gt;17943&quot;,
  9.          &quot;VersionID&quot;:2
  10.       }
  11.    ]
  12. }&#39;

Statement:

  1. SELECT *
  2. FROM OPENJSON(@json, &#39;$.Fields&#39;) WITH (
  3.   FieldId VARCHAR(10) &#39;$.FieldId&#39;, 
  4.   FieldName VARCHAR(MAX) &#39;$.FieldName&#39;, 
  5.   FieldValue NVARCHAR(MAX) &#39;$.FieldValue&#39;
  6. ) j1
  7. CROSS APPLY OPENJSON(j1.FieldValue) WITH (
  8.   Web_Id varchar(50) &#39;$.Web_Id&#39;,
  9.   CCID varchar(50) &#39;$.CCID&#39;
  10. ) j2
FieldId FieldName FieldValue Web_Id CCID
17943 &lt;p>Primary_Product&lt;/p>17943 [{"Web_Id":"1;#99999984_6_11_018111","EDF":[{"key":"Orders","val":"1"}],"CCID":"1;#AVSPN004987","G":null}] 1;#99999984_6_11_018111 1;#AVSPN004987

Note, that if the $.FieldValue part of the stored JSON is stored as JSON array, not as a string, you will need the AS JSON modifier to get the nested JSON content.

JSON array as text:

  1. DECLARE @json1 nvarchar(max) = N&#39;{
  2.    &quot;Fields&quot;:[
  3.       {&quot;FieldValue&quot;:&quot;[{\&quot;key\&quot;:\&quot;value\&quot;}]&quot;}
  4.    ]
  5. }&#39;
  6. SELECT *
  7. FROM OPENJSON(@json1, &#39;$.Fields&#39;) WITH (
  8.   FieldValue NVARCHAR(MAX) &#39;$.FieldValue&#39;
  9. ) j1
  10. CROSS APPLY OPENJSON(j1.FieldValue) WITH (
  11.   [key] varchar(50) &#39;$.key&#39;
  12. ) j2

JSON array as JSON:

  1. DECLARE @json2 nvarchar(max) = N&#39;{
  2.    &quot;Fields&quot;:[
  3.       {&quot;FieldValue&quot;:[{&quot;key&quot;:&quot;value&quot;}]}
  4.    ]
  5. }&#39;
  6. SELECT *
  7. FROM OPENJSON(@json2, &#39;$.Fields&#39;) WITH (
  8.   FieldValue NVARCHAR(MAX) &#39;$.FieldValue&#39; AS JSON
  9. ) j1
  10. CROSS APPLY OPENJSON(j1.FieldValue) WITH (
  11.   [key] varchar(50) &#39;$.key&#39;
  12. ) j2
FieldValue key
[{"key":"value"}] value

huangapple
  • 本文由 发表于 2023年6月8日 16:17:41
  • 转载请务必保留本文链接：https://go.coder-hub.com/76429906.html
  • json
  • sql
  • sql-server
  • t-sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定