时间戳

首页 > 解决方案 > 要列出的 SQL 列值搜索文本

sql - 要列出的 SQL 列值搜索文本

问题描述

我想使用 SQL 从日志表中获取状态值作为列表。

响应列：（完整字符串）“

{"headers":{"date":"Wed, 07 Oct 2020 06:46:33 GMT","server":"RR/1.983","transfer-encoding":"chunked","vary":"Accept-Encoding","x-frame-options":"SAMEORIGIN;","access-control-allow-headers":"Content-Type, Authorization","strict-transport-security":"max-age\u003d63072000","access-control-allow-methods":"GET, POST, DELETE, PUT, PATCH, OPTIONS","access-control-allow-origin":"*","access-control-allow-credentials":"true","x-xss-protection":"1; mode\u003dblock","x-content-type-options":"nosniff","content-type":"application/json;charset\u003dutf-8","cache-control":"no-transform"},

"body":[
    {"index":114,"status":"success","subRequestId":"0051"}},
    {"index":114,"status":"failure","subRequestId":"0052"}},
    {"index":114,"status":"failure","subRequestId":"0053"}},
    
],"status":200}

"

我想得到：

{"index":114,"status":"failure","subRequestId":"0052"}}
{"index":114,"status":"failure","subRequestId":"0053"}}

我试过了：

select log.RESPONSE
from LOG_TABLE log
where log.RESPONSE like ('%failure%');

我怎样才能用sql做到这一点，你能帮忙吗？

标签： sqloracle

解决方案

请指定您的数据库供应商。对于 Postgres，请使用Postgres JSON 函数。

您可以使用此查询（db fiddle）来实现您请求的结果：

with log (response) as (values('{"headers":{},
"body":[
    {"index":114,"status":"success","subRequestId":"0051"},
    {"index":114,"status":"failure","subRequestId":"0052"},
    {"index":114,"status":"failure","subRequestId":"0053"}
],"status":200}'::json)
)
select x.*
from log, json_array_elements(log.response->'body') as x(v)
where x.v->>'status' like '%failure%'

对于更复杂的情况，您可能希望打破内部 json 以设置使用json_to_recordset，然后将其组装回 json。

指定供应商后更新：然后使用Oracle 函数（db fiddle）：

with log (response) as (select '{"headers":{},
"body":[
    {"index":114,"status":"success","subRequestId":"0051"},
    {"index":114,"status":"failure","subRequestId":"0052"},
    {"index":114,"status":"failure","subRequestId":"0053"}
],"status":200}' from dual
)
select json_object('index' value "index",'status' value "status",'subRequestId' value "subRequestId")
from log, json_table(response, '$.body[*]' columns (
  "index" number path '$.index',
  "status" varchar2(4000) path '$.status',
  "subRequestId" varchar2(4000) path '$.subRequestId'
)) x
where x."status" like '%failure%'

或更短的版本相当于上面的 Postgres 方言（db fiddle）：

select x.*
from log
   , json_table(response, '$.body[*]' columns (
       obj varchar2(4000) format json path '$'
     )) as x
where json_value(x.obj,'$.status') like '%failure%'

注意自 v.12 起添加了 JSON 支持。

推荐阅读