postgresql - 提炼 ?成行的 JSON 数组对象数
问题描述
所以我试图将一些大型 Json 数组转换为逐行格式(postgres db)。所以想象一下
{“searchResults”:[{“id”:“89705”,“score”:42,“title”:“Foo.”,“properties”:{…
目前我们在一个数组中的最多结果是大约 300~ id 并明确表示;
Data::Json->'searchResults'->0->'tags'->0->>'label' as "Tag - Result 1",
...
Data::Json->'searchResults'->300->'tags'->0->>'label' as "Tag - Result
1",
理想输出
Array, ID , Score, Title
----
1 89705, 42, foo
1 89706, 34, bar
2 90003, 54, thing
2 98594, 53, that
(所以 1,2 代表初始表中的不同行,它们都包含 ??? JSON 数据数组的对象数量)
扩展 JSON
{
"searchResults": [
{
"id": "897096",
"score": 42,
"title": "foo.",
"properties": {
"@type": "blah",
},
"publishedDate": "2018-06-30T10:20:20.555040Z",
"comments": [
{
"content": "",
"owner": {
"firstName": "",
"id": 0,
"lastName": ""
},
"id": 0,
"contentType": "",
"documentPk": 0,
"workflowStep": 0,
"order": 0
}
],
"tags": [
{
"tag": 783,
"label": "NO",
"iconClass": "",
"subGroup": "",
"exclude": false
},
{
"tag": 786,
"label": "Different name",
"iconClass": "",
"subGroup": "",
"exclude": false
}
],
"reviewTags": [
{
"tag": 2,
"label": "Accept",
"iconClass": "",
"subGroup": "",
"exclude": false
}
],
"original": {
..."names": [
{
"full_name": "This name"
}
],
"Entry Type": "Organization",
"Last Updated": "2018/05/03",
"Hit Category": "N/A",
"Aliases": [
"Olaj",
"hbhbhb"
]
},
"snippet": "",
"url": "",
"source": "_"
},
{
"id": "879057",
"score": 36,
"title": "name of company",
"properties": {
"@type": "",
"category": "SOE",
"type": "Organization",
"country": "Korea, Republic Of",
"subcategory": ""
},
"publishedDate": "2018-05-31T10:20:20.559714Z",
"comments": [
{
"content": "",
"owner": {
"firstName": "",
"id": 0,
"lastName": ""
},
"id": 0,
"contentType": "",
"documentPk": 0,
"workflowStep": 0,
"order": 0
}
],
"tags": [
{
"tag": 783,
"label": "NO",
"iconClass": "",
"subGroup": "",
"exclude": false
},
{
"tag": 786,
"label": "Different name",
"iconClass": "",
"subGroup": "",
"exclude": false
}
关于我的选择有什么建议吗?
谢谢@a_horse_with_no_name 这工作得很好。
解决方案
我不清楚 JSON 如何在数组中继续,但您似乎正在寻找这样的东西:
with testdata(props) as (
values (
'{"searchResults":
[
{"id":"89705","score":42,"title":"Foo"},
{"id":"89706","score":34, "title":"bar"}
]}'::jsonb
)
)
select x.idx,
x.val ->> 'id' as id,
x.val ->> 'score' as score,
x.val ->> 'title' as title
from testdata, jsonb_array_elements(props -> 'searchResults') with ordinality as x (val, idx);
返回
idx | id | score | title
----+-------+-------+------
1 | 89705 | 42 | Foo
2 | 89706 | 34 | bar
推荐阅读
- node.js - Vue:使用 vue-cli 将请求代理到单独的后端服务器
- session - Windows 的控制台会话中未加载 CredentialProvider
- css - 试图制作一个居中的 - 除非太长 - 一行,使用css的图片标题
- go - 如何使用两个客户端。请使用 golang
- python - 使用 python 在 stanford-nlp 中的回指解析
- css - 悬停在按钮元素上不起作用
- amazon-web-services - 创建 ELB WAF 规则的 Lambda
- python - 具有交叉熵损失的 Softmax 激活导致两个类的输出分别准确地收敛到 0 和 1
- r - 通过 SQL Server 2017 使用 R 进行回归分析
- php - $new 作为 php 关键字中的变量