postgresql Postgres 9.5 -查询嵌套JSON元素的数组长度

liwlm1x9 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(2)|浏览(131)

我有一个Postgres表，其中包含一个JSON字段，该字段标识与给定记录关联的图像。

{"photo-verification": 
   {"photos": [
     {"type": "photo-verification", "fileName": "4f35a880-e9a0-43f9-a31e-1bb8b765d04d", "mimeType": "image/jpeg", "createdBy": "jmlittm", "createdTs": "2016-06-20T20:25:39.706Z", "delFlag": false, "updatedBy": "jmlittm", "updatedTs": "2016-06-20T20:25:39.706Z"},
     {"type": "photo-verification", "fileName": "3a104d07-dc48-4f59-b83f-06cd35a21dae", "mimeType": "image/jpeg", "createdBy": "jmlittm", "createdTs": "2016-06-20T22:31:09.808Z", "delFlag": false, "updatedBy": "jmlittm", "updatedTs": "2016-06-20T22:31:09.808Z"}
     ]
   }
}

一个记录可以有0个或多个关联的图像-如果没有图像，那么整个字段将为空。我尝试编写一个查询来确定有多少图像与给定记录关联。最终结果应该是多少记录有一个图像，多少记录有多个图像。如果我查询JSON的顶层，如下所示：
select n.images->'photo-verification' from notes n;
我可以得到包含照片数组的内部JSON，但是如果我尝试深入挖掘，我得不到任何结果。
select array_length(n.images->'photo-verification'->'photos', 1) from notes n;
或
select json_array_length(n.images->'photo-verification'->'photos') from notes n;
但我最终得到了错误和一个暗示，也许我应该考虑选角。
我刚刚开始深入研究Postgres，所以我仍然在努力理解查询语言的一些细节，我会继续研究，但任何人可能提供的帮助或见解将非常感谢。
编辑：
因此，我想我可以通过创建一个只包含'photos' JSON的视图并过滤掉所有空字段来简化这个问题：

CREATE VIEW photos as SELECT n.images->'photo-verification' as photo FROM notes.notes n where (n.images->'photo-verification')::text != '';

它起作用了，因为我现在有了一个包含JSON列的视图，如下所示：

{"photos": [
     {"type": "photo-verification", "fileName": "4f35a880-e9a0-43f9-a31e-1bb8b765d04d", "mimeType": "image/jpeg", "createdBy": "jmlittm", "createdTs": "2016-06-20T20:25:39.706Z", "delFlag": false, "updatedBy": "jmlittm", "updatedTs": "2016-06-20T20:25:39.706Z"},
     {"type": "photo-verification", "fileName": "3a104d07-dc48-4f59-b83f-06cd35a21dae", "mimeType": "image/jpeg", "createdBy": "jmlittm", "createdTs": "2016-06-20T22:31:09.808Z", "delFlag": false, "updatedBy": "jmlittm", "updatedTs": "2016-06-20T22:31:09.808Z"}
     ]
   }

但是如果我尝试
select json_array_length(photo) from photos;
我得到：
ERROR: cannot get array length of a scalar
如果我尝试
select json_array_length(photo->'photos') from photos;
我得到一堆空白记录。
我一定是漏掉了什么...

postgresql

来源：https://stackoverflow.com/questions/41085839/postgres-9-5-querying-array-length-of-a-nested-json-element

2条答案

按热度按时间

tct7dpnv1#

create temp table t (id int, data json);
insert into t values 
(1, 
'{"photo-verification": 
   {"photos": [
     {"type": "photo-verification", "fileName": "4f35a880-e9a0-43f9-a31e-1bb8b765d04d", "mimeType": "image/jpeg", "createdBy": "jmlittm", "createdTs": "2016-06-20T20:25:39.706Z", "delFlag": false, "updatedBy": "jmlittm", "updatedTs": "2016-06-20T20:25:39.706Z"},
     {"type": "photo-verification", "fileName": "3a104d07-dc48-4f59-b83f-06cd35a21dae", "mimeType": "image/jpeg", "createdBy": "jmlittm", "createdTs": "2016-06-20T22:31:09.808Z", "delFlag": false, "updatedBy": "jmlittm", "updatedTs": "2016-06-20T22:31:09.808Z"}
     ]
   }
}'),
(2, 
'{"photo-verification": 
   {"photos": [
     {"type": "photo-verification", "fileName": "4f35a880-e9a0-43f9-a31e-1bb8b765d04d", "mimeType": "image/jpeg", "createdBy": "jmlittm", "createdTs": "2016-06-20T20:25:39.706Z", "delFlag": false, "updatedBy": "jmlittm", "updatedTs": "2016-06-20T20:25:39.706Z"}
     ]
   }
}')
;

select id, json_array_length(data->'photo-verification'->'photos')
from t;

或者，如果您对某些特定领域感兴趣：

select json_array_elements(data->'photo-verification'->'photos')->>'fileName' as fileName
from t;

赞(0）回复(0）举报 2023-01-25

wj8zmpe12#

似乎需要强制转换为JSON：

select json_array_length((images->'photo-verification'->'photos')::json)
from notes;

赞(0）回复(0）举报 2023-01-25

我来回答

postgresql Postgres 9.5 -查询嵌套JSON元素的数组长度

2条答案

相关问题

热门标签

最新问答