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

Question

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

11

我有一个 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，所以仍在努力理解查询语言的一些细节。我将继续研究，但如果有人能提供任何帮助或见解，那将不胜感激。

编辑：

因此，我认为通过创建仅具有“照片”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;

会得到一堆空记录。

我一定漏了什么......

- Trevor Turney

2个回答

7

似乎需要将其转换为JSON格式：

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

- denarced

好的，我尝试了 select count(*) from notes where json_array_length((images->'photo-verification'->'photos')::json) > 1;，但返回的计数为零。不过看起来这应该是正确的方向。感谢您的帮助！ - Trevor Turney

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- McNets · Accepted Answer

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;