如何从Oracle 19c的CLOB中提取整个JSON元素

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

How to extract an entire JSON element from Oracle 19c CLOB

问题

我有一个包含JSON数据的表，数据存储在一个CLOB字段中。我们从外部来源获取这些数据，最近它们更改了一些格式，导致我们的后续处理出现问题。

数据包含一个包含用户角色的对象，当格式正确时，包含与个人角色关联的位置数组。

问题出现在一个用户具有一个角色（Test Role 1），其位置仅包含花括号{}，而另一个用户也具有相同的角色（Test Role 1），其位置包含有效数据时。当我们查询第二个用户的角色时，我们会得到该用户的位置数据为空。

在示例数据中，有员工ID 1（Whitbuckle, Dalongrirlum），他具有Test Role 1和Test Role 2的角色，每个角色都有一个位置{}，以及员工ID 2（Longblade, Skolout），他具有Test Role 1的角色并包含有效位置数据。其他两个用户要么具有空的EntitlementJSON属性，要么具有包含有效位置数据的Test Role 3。

当我们查询数据时，员工ID 2的记录具有空的角色，即使我们明确选择只选择他们的员工ID。

请求的解决方案：
我正在编写一个验证过程，以确保识别出格式不正确的行。为此，我想选择一个用户的EntitlementJSON属性的内容到一个变量中。然后，我将检查是否存在"location":{}。如果存在，这是一个不良记录。例如，我想要看到员工ID 1的内容如下：

  1.       "Test Role 1": {
  2.         "dodaac": {},
  3.         "fundCode": {},
  4.         "glRepair": {},
  5.         "location": {},
  6.         "cognos": {},
  7.         "jv": {}
  8.       },
  9.       "Test Role 2": {
  10.         "dodaac": {},
  11.         "fundCode": {},
  12.         "glRepair": {},
  13.         "location": {},
  14.         "cognos": {},
  15.         "jv": {}
  16.       }

这里有一个示例 db<>fiddle

  1. CREATE TABLE TEST_JSON
  2. 	(	 PROCESS_ID              NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  3. 	   JSON_DATA		 		 			 CLOB		CONSTRAINT check_json CHECK (JSON_DATA IS JSON)
  4. 	)
  5. LOB (JSON_DATA) STORE AS SECUREFILE (
  6.   ENABLE      STORAGE IN ROW
  7.   CHUNK       8192
  8.   RETENTION
  9.   NOCACHE
  10.   LOGGING);
  12. -- TABLE ALTERS
  13. ALTER TABLE TEST_JSON 
  14.   ADD CONSTRAINT TEST_JSON_PK 
  15.   PRIMARY KEY ( PROCESS_ID ) USING INDEX
  16.   ENABLE;
  18. set serveroutput on
  20. declare
  21.       c 				     clob;
  22.   BEGIN
  24.     c:= to_clob('[
  25.       {
  26.         "displayName": "Whitbuckle, Dalongrirlum",
  27.         "employeeID": "1",
  28.         "EntitlementJSON": {
  29.           "Test Role 1": {
  30.             "dodaac": {},
  31.             "fundCode": {},
  32.             "glRepair": {},
  33.             "location": {},
  34.             "cognos": {},
  35.             "jv": {}
  36.           },
  37.           "Test Role 2": {
  38.             "dodaac": {},
  39.             "fundCode": {},
  40.             "glRepair": {},
  41.             "location": {},
  42.             "cognos": {},
  43.             "jv": {}
  44.           }
  45.         },
  46.         "manager": "Urgaehilde Rubyforged",
  47.         "company": "Bloodguard Industrie"
  48.       },
  49.       {
  50.         "displayName": "Koboldbelly, Sitgrolin",
  51.         "employeeID": "4",
  52.         "EntitlementJSON": {},
  53.         "manager": "Kogrubera Orcborn",
  54.         "company": "Bloodguard Industrie"
  55.      },
  56.      {
  57.         "displayName": "Longblade, Skolout",
  58.         "employeeID": "2",
  59.         "EntitlementJSON": {
  60.           "Test Role 1": {
  61.             "location": [
  62.               "Rockwall Villa - RV",
  63.               "Thunderbluff - TB"
  64.             ]
  65.           }
  66.         },
  67.         "manager": "Therrilyn Mithrilpike",
  68.         "company": "Bloodguard Industrie"
  69.       },
  70.       {
  71.         "displayName": "Warmcoat, Alfomdum",
  72.         "employeeID": "3",
  73.         "EntitlementJSON": {
  74.           "Test Role 3": {
  75.             "location": [
  76.               "ALL"
  77.             ]
  78.           }
  79.         },
  80.         "manager": "Therrilyn Mithrilpike",
  81.         "company": "Bloodguard Industrie"
  82.       }
  83.     ]');
  84.     
  85.     INSERT INTO TEST_JSON (JSON_DATA)
  86.     VALUES (c);
  87.     commit;
  88.   END;

以下是我们运行的查询：

  1. select process_id,
  2.      display_name,
  3.      employeeID,
  4.      manager,
  5.      listagg(TR1) within group (order by process_id, display_name, employeeID, manager) Role_TR1,
  6.      listagg(TR2) within group (order by process_id, display_name, employeeID, manager) Role_TR2,
  7.      listagg(TR3) within group (order by process_id, display_name, employeeID, manager) Role_TR3,
  8.      listagg(TR4) within group (order by process_id, display_name, employeeID, manager) Role_TR4
  9. from (select j.process_id,
  10.              jt.display_Name,
  11.              jt.employeeID,                        
  12.              jt.manager,                            
  13.              TR1,
  14.              TR2,
  15.              TR3,
  16.              TR4
  17.         from test_json j
  18.              cross apply JSON_TABLE(j.JSON_DATA, '$[*]'
  19.                COLUMNS (display_Name                  VARCHAR2(200 CHAR)  PATH '$.displayName',
  20.                         employeeID                    VARCHAR2(20 CHAR) PATH '$.employeeID',
  21.                         manager                       VARCHAR2(200 CHAR) PATH '$.manager',       
  22.                         nested path '$.EntitlementJSON."Test Role 1"' columns
  23.                           (TR1 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),      
  24.                         nested path '$.EntitlementJSON."Test Role 2"' columns
  25.                           (TR2 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),      
  26.                         nested path '$.EntitlementJSON."Test Role 3"' columns
  27.                           (TR3 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),      
  28.                         nested path '$.EntitlementJSON."Test Role 4"' columns
  29.                           (TR4 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]')      
  30.                       )) jt
  31.         where process_id = 1)
  32.   --and jt.employeeID = '2')
  33. group by process_id, employeeID, display_name, manager;

即使取消注释"and jt.employeeID = '2'"行，我们仍然

英文:

I have a table with JSON data stored in a CLOB. We get this data from an external source and recently they changed some formatting which causes issues with our post processing.
The data contains an object containing user roles and, when correctly formatted, arrays of locations associated to an individual role.

The problem arises when one user has a Role (Test Role 1) with a Location with just curly braces {} and another user also has the same role (Test Role 1) with a location with valid data. When we query the second user's roles we get null location date for that user.

In the sample data there is employeeID 1 (Whitbuckle, Dalongrirlum) who has roles of Test Role 1 and Test Role 2, each with a Location {} and employeeID 2 (Longblade, Skolout) with a role of Test Role 1 with valid locations. The other two users have either an empty EntitlementJSON attribute or Test Role 3 with valid location data.

When we query the data, employeeID 2 record has null roles even if we explicitly select only their employeeID.

Requested Solution:
I am writing a validation procedure to makes sure that rows with the bad formatting gets identified. To do this, I would like to select into a variable the contents of the EntitlementJSON attribute for a single user. I would then check for the existance of "location":{}. If it exists this is a bad record. For example, what I would like to see for employeeID 1 is:

> "Test Role 1": {
> "dodaac": {},
> "fundCode": {},
> "glRepair": {},
> "location": {},
> "cognos": {},
> "jv": {}
> },
> "Test Role 2": {
> "dodaac": {},
> "fundCode": {},
> "glRepair": {},
> "location": {},
> "cognos": {},
> "jv": {}
> }

There is an example at this db<>fiddle

Code samples

  1. CREATE TABLE TEST_JSON
  2. (	 PROCESS_ID              NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  3. JSON_DATA		 		 			 CLOB		CONSTRAINT check_json CHECK (JSON_DATA IS JSON)
  4. )
  5. LOB (JSON_DATA) STORE AS SECUREFILE (
  6. ENABLE      STORAGE IN ROW
  7. CHUNK       8192
  8. RETENTION
  9. NOCACHE
  10. LOGGING);
  11. -- TABLE ALTERS
  12. ALTER TABLE TEST_JSON 
  13. ADD CONSTRAINT TEST_JSON_PK 
  14. PRIMARY KEY ( PROCESS_ID ) USING INDEX
  15. ENABLE;
  16. set serveroutput on
  17. declare
  18. c 				     clob;
  19. BEGIN
  20. c:= to_clob(&#39;[
  21. {
  22. &quot;displayName&quot;: &quot;Whitbuckle, Dalongrirlum&quot;,
  23. &quot;employeeID&quot;: &quot;1&quot;,
  24. &quot;EntitlementJSON&quot;: {
  25. &quot;Test Role 1&quot;: {
  26. &quot;dodaac&quot;: {},
  27. &quot;fundCode&quot;: {},
  28. &quot;glRepair&quot;: {},
  29. &quot;location&quot;: {},
  30. &quot;cognos&quot;: {},
  31. &quot;jv&quot;: {}
  32. },
  33. &quot;Test Role 2&quot;: {
  34. &quot;dodaac&quot;: {},
  35. &quot;fundCode&quot;: {},
  36. &quot;glRepair&quot;: {},
  37. &quot;location&quot;: {},
  38. &quot;cognos&quot;: {},
  39. &quot;jv&quot;: {}
  40. }
  41. },
  42. &quot;manager&quot;: &quot;Urgaehilde Rubyforged&quot;,
  43. &quot;company&quot;: &quot;Bloodguard Industrie&quot;
  44. },
  45. {
  46. &quot;displayName&quot;: &quot;Koboldbelly, Sitgrolin&quot;,
  47. &quot;employeeID&quot;: &quot;4&quot;,
  48. &quot;EntitlementJSON&quot;: {},
  49. &quot;manager&quot;: &quot;Kogrubera Orcborn&quot;,
  50. &quot;company&quot;: &quot;Bloodguard Industrie&quot;
  51. },
  52. {
  53. &quot;displayName&quot;: &quot;Longblade, Skolout&quot;,
  54. &quot;employeeID&quot;: &quot;2&quot;,
  55. &quot;EntitlementJSON&quot;: {
  56. &quot;Test Role 1&quot;: {
  57. &quot;location&quot;: [
  58. &quot;Rockwall Villa - RV&quot;,
  59. &quot;Thunderbluff - TB&quot;
  60. ]
  61. }
  62. },
  63. &quot;manager&quot;: &quot;Therrilyn Mithrilpike&quot;,
  64. &quot;company&quot;: &quot;Bloodguard Industrie&quot;
  65. },
  66. {
  67. &quot;displayName&quot;: &quot;Warmcoat, Alfomdum&quot;,
  68. &quot;employeeID&quot;: &quot;3&quot;,
  69. &quot;EntitlementJSON&quot;: {
  70. &quot;Test Role 3&quot;: {
  71. &quot;location&quot;: [
  72. &quot;ALL&quot;
  73. ]
  74. }
  75. },
  76. &quot;manager&quot;: &quot;Therrilyn Mithrilpike&quot;,
  77. &quot;company&quot;: &quot;Bloodguard Industrie&quot;
  78. }
  79. ]&#39;);
  80. INSERT INTO TEST_JSON (JSON_DATA)
  81. VALUES (c);
  82. commit;
  83. END;

Here is the query we run:

  1. select process_id,
  2. display_name,
  3. employeeID,
  4. manager,
  5. listagg(TR1) within group (order by process_id, display_name, employeeID, manager) Role_TR1,
  6. listagg(TR2) within group (order by process_id, display_name, employeeID, manager) Role_TR2,
  7. listagg(TR3) within group (order by process_id, display_name, employeeID, manager) Role_TR3,
  8. listagg(TR4) within group (order by process_id, display_name, employeeID, manager) Role_TR4
  9. from (select j.process_id,
  10. jt.display_Name,
  11. jt.employeeID,                        
  12. jt.manager,                            
  13. TR1,
  14. TR2,
  15. TR3,
  16. TR4
  17. from test_json j
  18. cross apply JSON_TABLE(j.JSON_DATA, &#39;$[*]&#39;
  19. COLUMNS (display_Name                  VARCHAR2(200 CHAR)  PATH &#39;$.displayName&#39;,
  20. employeeID                    VARCHAR2(20 CHAR) PATH &#39;$.employeeID&#39;,
  21. manager                       VARCHAR2(200 CHAR) PATH &#39;$.manager&#39;,       
  22. nested path &#39;$.EntitlementJSON.&quot;Test Role 1&quot;&#39; columns
  23. (TR1 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH &#39;$.location[*]&#39;),      
  24. nested path &#39;$.EntitlementJSON.&quot;Test Role 2&quot;&#39; columns
  25. (TR2 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH &#39;$.location[*]&#39;),      
  26. nested path &#39;$.EntitlementJSON.&quot;Test Role 3&quot;&#39; columns
  27. (TR3 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH &#39;$.location[*]&#39;),      
  28. nested path &#39;$.EntitlementJSON.&quot;Test Role 4&quot;&#39; columns
  29. (TR4 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH &#39;$.location[*]&#39;)      
  30. )) jt
  31. where process_id = 1)
  32. --and jt.employeeID = &#39;2&#39;)
  33. group by process_id, employeeID, display_name, manager;

Even when we un-comment the "and jt.employeeID = '2'" line we still get null locations for employeeID 2

答案1

得分: 1

你不需要汇总或使用 NESTED PATH：

  1. SELECT process_id,
  2. display_name,
  3. employeeID,
  4. manager,
  5. tr1,
  6. tr2,
  7. tr3,
  8. tr4
  9. from   test_json j
  10. CROSS APPLY JSON_TABLE(
  11. j.JSON_DATA, '$[*]'
  12. COLUMNS (
  13. display_Name VARCHAR2(200 CHAR) PATH '$.displayName',
  14. employeeID   VARCHAR2(20 CHAR)  PATH '$.employeeID',
  15. manager      VARCHAR2(200 CHAR) PATH '$.manager',
  16. tr1         JSON               PATH '$.EntitlementJSON."Test Role 1".location',
  17. tr2         JSON               PATH '$.EntitlementJSON."Test Role 2".location',
  18. tr3         JSON               PATH '$.EntitlementJSON."Test Role 3".location',
  19. tr4         JSON               PATH '$.EntitlementJSON."Test Role 4".location'
  20. )
  21. ) e
  22. WHERE  j.process_id = 1
  23. AND    e.employeeID = '2';

对于示例数据，输出如下：

PROCESS_ID DISPLAY_NAME EMPLOYEEID MANAGER TR1 TR2 TR3 TR4
1 Longblade, Skolout 2 Therrilyn Mithrilpike ["Rockwall Villa - RV","Thunderbluff - TB"] null null null

fiddle

英文:

You don't need to aggregate or use NESTED PATH:

  1. SELECT process_id,
  2. display_name,
  3. employeeID,
  4. manager,
  5. tr1,
  6. tr2,
  7. tr3,
  8. tr4
  9. from   test_json j
  10. CROSS APPLY JSON_TABLE(
  11. j.JSON_DATA, &#39;$[*]&#39;
  12. COLUMNS (
  13. display_Name VARCHAR2(200 CHAR) PATH &#39;$.displayName&#39;,
  14. employeeID   VARCHAR2(20 CHAR)  PATH &#39;$.employeeID&#39;,
  15. manager      VARCHAR2(200 CHAR) PATH &#39;$.manager&#39;,
  16. tr1         JSON               PATH &#39;$.EntitlementJSON.&quot;Test Role 1&quot;.location&#39;,
  17. tr2         JSON               PATH &#39;$.EntitlementJSON.&quot;Test Role 2&quot;.location&#39;,
  18. tr3         JSON               PATH &#39;$.EntitlementJSON.&quot;Test Role 3&quot;.location&#39;,
  19. tr4         JSON               PATH &#39;$.EntitlementJSON.&quot;Test Role 4&quot;.location&#39;
  20. )
  21. ) e
  22. WHERE  j.process_id = 1
  23. AND    e.employeeID = &#39;2&#39;;

Which, for the sample data, outputs:

PROCESS_ID DISPLAY_NAME EMPLOYEEID MANAGER TR1 TR2 TR3 TR4
1 Longblade, Skolout 2 Therrilyn Mithrilpike ["Rockwall Villa - RV","Thunderbluff - TB"] null null null

fiddle

huangapple
  • 本文由 发表于 2023年2月19日 02:47:38
  • 转载请务必保留本文链接：https://go.coder-hub.com/75495613.html
  • json
  • oracle19c
匿名

发表评论

匿名网友

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

确定