json parsing with oracle regexp

Question

I'm trying to pick from json string the content of the nearest key "name" after every occurance of substring Epic Link in given json string.

I've read the ansvers regarding extracting json values fom oracle. When you just need content of the known key, that's not so challenging, but my case is different:

1. I need to pick the values of key "name" if and only if that key is the first appearance of key "name" after the appearance of substring Epic Link in json body
2. if there is more than one appearance of substring Epic Link in sample I need to exctract the content first key "name" for each apperance of substring Epic Link

regexp

(?<=\bEpic Link\s)*"name":"([^"]*)".*

works on testing site but returns whole rest of the string after tag name first occured after substring when i try it in Oracle.

my sample data are

[{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"filter = \"JDoc_все фичи\"  and issue not in havingLinkedIssuesFromFilter(\"JDoc_все фичи\",\"Epic Link\", outward)","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-1","name":"Features without links"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"(filter = \"JDoc_все задачи\" and issue not in linkedIssuesFromFilter(\"JDoc_все фичи\"))","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-2","name":"Стори без фичи"}},{"module":"com.almworks.jira.structure:grouper-field","params":{"fieldId":"status"},"key":"grouper","quick":{"id":"1321-4","name":"By Status"}}][{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"filter = \"JDoc_все фичи\"  and issue not in havingLinkedIssuesFromFilter(\"JDoc_все фичи\",\"Epic Link\", outward)","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-1","name":"Another Features without links"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"(filter = \"JDoc_все задачи\" and issue not in linkedIssuesFromFilter(\"JDoc_все фичи\"))","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-2","name":"Стори без фичи"}},{"module":"com.almworks.jira.structure:grouper-field","params":{"fieldId":"status"},"key":"grouper","quick":{"id":"1321-4","name":"By Status"}}]

and I expect

Features without links

and

Another Features without links

to be returned

Answer 1

Some of the syntax in your expression (abbreviations, look-behind, greediness) is either not supported in Oracle or does not work the same way in Oracle. Please refer to Using Regular Expressions in Database Applications for information on how to build an expression that will work as expected in Oracle.

WITH json_sample
AS
(
SELECT '[{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"filter = \"JDoc_все фичи\"  and issue not in havingLinkedIssuesFromFilter(\"JDoc_все фичи\",\"Epic Link\", outward)","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-1","name":"Features without links"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"(filter = \"JDoc_все задачи\" and issue not in linkedIssuesFromFilter(\"JDoc_все фичи\"))","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-2","name":"Стори без фичи"}},{"module":"com.almworks.jira.structure:grouper-field","params":{"fieldId":"status"},"key":"grouper","quick":{"id":"1321-4","name":"By Status"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"filter = \"JDoc_все фичи\"  and issue not in havingLinkedIssuesFromFilter(\"JDoc_все фичи\",\"Epic Link\", outward)","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-1","name":"Another Features without links"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"(filter = \"JDoc_все задачи\" and issue not in linkedIssuesFromFilter(\"JDoc_все фичи\"))","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-2","name":"Стори без фичи"}},{"module":"com.almworks.jira.structure:grouper-field","params":{"fieldId":"status"},"key":"grouper","quick":{"id":"1321-4","name":"By Status"}}]' json_col
      ,'\WEpic Link\W.*?"name":"[^"]*"' regexp_find_str
      ,'^.*"([^"]*)"$' regexp_replace_str
FROM DUAL
)
SELECT REGEXP_REPLACE(REGEXP_SUBSTR(js.json_col, js.regexp_find_str, 1, LEVEL), js.regexp_replace_str, '\1') name_value
FROM   json_sample js
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(js2.json_col, js2.regexp_find_str) FROM json_sample js2)

If the input is a valid JSON array and it has a consistent structure, it might be easier to use JSON_TABLE to get the values. The query below assumes the input to be a single JSON array. There was a modification to the sample data to put all array values in one JSON array.

SELECT jt.quick_name
FROM   JSON_TABLE('[{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"filter = \"JDoc_все фичи\"  and issue not in havingLinkedIssuesFromFilter(\"JDoc_все фичи\",\"Epic Link\", outward)","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-1","name":"Features without links"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"(filter = \"JDoc_все задачи\" and issue not in linkedIssuesFromFilter(\"JDoc_все фичи\"))","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-2","name":"Стори без фичи"}},{"module":"com.almworks.jira.structure:grouper-field","params":{"fieldId":"status"},"key":"grouper","quick":{"id":"1321-4","name":"By Status"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"filter = \"JDoc_все фичи\"  and issue not in havingLinkedIssuesFromFilter(\"JDoc_все фичи\",\"Epic Link\", outward)","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-1","name":"Another Features without links"}},{"module":"com.almworks.jira.structure:filter-jql","params":{"showSubItems":false,"jql":"(filter = \"JDoc_все задачи\" and issue not in linkedIssuesFromFilter(\"JDoc_все фичи\"))","level":1,"keepNonIssues":false},"key":"filter","quick":{"id":"1321-2","name":"Стори без фичи"}},{"module":"com.almworks.jira.structure:grouper-field","params":{"fieldId":"status"},"key":"grouper","quick":{"id":"1321-4","name":"By Status"}}]'
      , '$[*]'
       COLUMNS( params_jql VARCHAR2 PATH '$.params.jql'
               ,quick_name VARCHAR2 PATH '$.quick.name'
       )
      ) jt
WHERE REGEXP_LIKE(jt.params_jql, '\WEpic Link\W')