json - 查询嵌套 JSON 数组 PostrgreSQL 中的所有元素
问题描述
我正在尝试在 SQL 中创建一个查询来检索 DNS 答案信息,以便我可以通过添加 TimescaleDB 在 Grafana 中对其进行可视化。现在,我正在努力让 postgres 一次查询多个元素。我试图查询的 JSON 的结构如下所示:
{
"Z": 0,
"AA": 0,
"ID": 56559,
"QR": 1,
"RA": 1,
"RD": 1,
"TC": 0,
"RCode": 0,
"OpCode": 0,
"answer": [
{
"ttl": 19046,
"name": "i.stack.imgur.com",
"type": 5,
"class": 1,
"rdata": "i.stack.imgur.com.cdn.cloudflare.net"
},
{
"ttl": 220,
"name": "i.stack.imgur.com.cdn.cloudflare.net",
"type": 1,
"class": 1,
"rdata": "104.16.30.34"
},
{
"ttl": 220,
"name": "i.stack.imgur.com.cdn.cloudflare.net",
"type": 1,
"class": 1,
"rdata": "104.16.31.34"
},
{
"ttl": 220,
"name": "i.stack.imgur.com.cdn.cloudflare.net",
"type": 1,
"class": 1,
"rdata": "104.16.0.35"
}
],
"ANCount": 13,
"ARCount": 0,
"QDCount": 1,
"question": [
{
"name": "i.stack.imgur.com",
"qtype": 1,
"qclass": 1
}
]
}
可以有任意数量的答案,包括零,所以我想找出一种查询所有答案的方法。例如,我正在尝试从每个索引答案中检索 ttl 字段,并且我可以查询特定索引,但无法查询所有匹配项。
这适用于查询单个索引:
SELECT (data->'answer'->>0)::json->'ttl'
FROM dns;
当我环顾四周时,我发现这是查询数组中所有索引的潜在解决方案,但它似乎不起作用并告诉我“无法从标量中提取元素”:
SELECT answer->>'ttl' ttl
FROM dns, jsonb_array_elements(data->'answer') answer, jsonb_array_elements(answer->'ttl') ttl
解决方案
使用jsonb_array_elements()
将为answer
数组中的每个对象提供一行。然后,您可以取消引用该对象:
select a.obj->>'ttl' as ttl, a.obj->>'name' as name, a.obj->>'rdata' as rdata
from dns d
cross join lateral jsonb_array_elements(data->'answer') as a(obj)
推荐阅读
- wordpress - Wordpress 文章三分之一部分中的组件
- html - CSS中的水平滚动
- jackson - 在流数据解析时从 JSON 模式中选择任何元素
- c# - 如何修复“列的类型值与先前观察到的类型不同”
- reactjs - 如何防止在 ssr 中首次加载 react-loadable?
- r - count() 的值除以一个数字
- julia - 如何让 Rosenbrock23 在 ParameterizedFunctions.jl DSL 中使用 ODE?
- rancher - 在 Rancher 堆栈中部署单个服务
- android - SyncAdapter onPerformSync 仅第一次调用
- wordpress - Wordpress - 自定义 PHP 函数,用于将 XML 中的多个节点保存到多个用户元字段中