使用Oracle正则表达式解析JSON

我试图从json字符串中挑选最近的关键字**“name”的内容，每次出现子字符串Epic Link后，在给定的json字符串。
我读过一些关于从oracle中提取json值的回答，当你只需要已知键的内容时，这并不是很有挑战性，但我的情况不同：
1.当且仅当键“name”是json body中出现子字符串Epic Link之后第一次出现的键“name”时，我需要选取该键的值
1.如果样本中出现了多次子字符串Epic Link**，我需要为每次出现的子字符串Epic Link提取内容第一个关键字**“name”**
正则表达式

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

在测试站点上工作，但当我在Oracle中尝试它时，在子字符串后第一次出现标记名后返回整个字符串的其余部分。
我的样本数据是

[{"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"}}]

我希望

Features without links

以及

Another Features without links

待退回

Oracle不支持表达式中的某些语法（缩写、look-behind、greediness），或者在Oracle中的工作方式不同。有关如何构建在Oracle中按预期工作的表达式的信息，请参阅Using Regular Expressions in Database Applications。

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)

如果输入是一个有效的JSON数组，并且结构一致，那么使用JSON_TABLE获取值可能会更容易。下面的查询假设输入是一个JSON数组。对示例数据进行了修改，将所有数组值放在一个JSON数组中。

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')

使用Oracle正则表达式解析JSON

1条答案

相关问题

热门标签

最新问答