I'm puzzled why my query hangs.

I have a simple regexp statement. The count works. The substr works. But when I add connect by syntax, the query hangs.

Query to find first occurrence, returns virtualDomains.GZATPAC_Get_Test_IDs"

select regexp_substr(model_view, 'virtualDomains\..*\"', 1,1)
FROM page
        WHERE 1=1
        AND id = 10815;

Query to find count of occurrences returns 3

select regexp_count(model_view, 'virtualDomains\..*\"')
FROM page
        WHERE 1=1
        AND id = 10815;

Using the following connect by level query hangs.

select regexp_substr(model_view, 'virtualDomains\..*\"', 1,level)
FROM page
        WHERE 1=1
        AND id = 10815
CONNECT BY level <= regexp_count(model_view, 'virtualDomains\..*\"');

Here is a sample of the text:

"{
   "components": [
      {
         "name": "title",
         "showInitially": true,
         "label": "GZATPAC-Test User Assignment",
         "type": "block"
      },
      {
         "resource": "virtualDomains.GZATPAC_Get_Test_IDs",
         "name": "GZATPAC_get_STUTEST_IDs",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_assign_proxy_users",
         "name": "GZATPAC_fill_assign_proxy_users",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_externalUserSearch",
         "name": "GZATPAC_search_for_proxy_users",
         "type": "resource",
         "staticData": []
      },
      {

P.S. I would like to strip out the final quote " in the string; although it works in several regex tools, unfortunately the following returns an empty row. Maybe an oracle limitation?

select regexp_substr(model_view, 'virtualDomains\..*(?:\")')
FROM page
        WHERE 1=1
        AND id = 10815;

CodePudding user response：

Don't use regular expressions to parse HTML JSON; use a proper parser:

SELECT value
FROM   page p
       CROSS APPLY JSON_TABLE(
         p.model_view,
         '$.components[*].resource'
         COLUMNS (
           value VARCHAR2(200) PATH '$'
         )
       )
WHERE  p.id = 10815;

Which, for the sample data:

CREATE TABLE page (
  id NUMBER,
  model_view CLOB CHECK (model_view IS JSON)
);

INSERT INTO page (id, model_view) VALUES (
  10815,
  '{
    "components": [
      {
         "name": "title",
         "showInitially": true,
         "label": "GZATPAC-Test User Assignment",
         "type": "block"
      },
      {
         "resource": "virtualDomains.GZATPAC_Get_Test_IDs",
         "name": "GZATPAC_get_STUTEST_IDs",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_assign_proxy_users",
         "name": "GZATPAC_fill_assign_proxy_users",
         "type": "resource",
         "staticData": []
      },
      {
         "resource": "virtualDomains.GZATPAC_externalUserSearch",
         "name": "GZATPAC_search_for_proxy_users",
         "type": "resource",
         "staticData": []
      }
    ]
  }');

Outputs:

VALUE
virtualDomains.GZATPAC_Get_Test_IDs
virtualDomains.GZATPAC_assign_proxy_users
virtualDomains.GZATPAC_externalUserSearch

db<>fiddle here