Postgres 9.5 - Querying array length of a nested JSON element

Question

I have a Postgres table that contains a JSON field, which identifies the images associated with the given record. The contents of the field look like this:

 {"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"}
     ]
   }
}

A record can have 0 or more associated images - if there are no images, then the whole field will be empty. I am trying to write a query to determine how many images are associated with a given record. The end result should be a count of how many records have one image, and how many have more than one. If I query the top level of the JSON, like so:

select n.images->'photo-verification' from notes n;

I can get the inner JSON, which contains the array of photos, but if I try to dig deeper, I get no results. My thought was that I could do something along the lines of

select array_length(n.images->'photo-verification'->'photos', 1) from notes n;

or

select json_array_length(n.images->'photo-verification'->'photos') from notes n;

but I end up getting errors and a hint that maybe I should consider casting.

I'm just starting my dive into Postgres, so I'm still trying to wrap my head around some of finer points of the query language. I will continue to research, but any help or insight that someone might provide would be greatly appreciated.

Edit:

So, I thought that I might simplify the problem by creating a view that only has the 'photos' JSON and filters out all of the empty fields:

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

It worked, in that I now have a view with a JSON column that looks like this:

   {"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"}
     ]
   }

However, if I try

select json_array_length(photo) from photos;

I get:

ERROR: cannot get array length of a scalar

If I try

select json_array_length(photo->'photos') from photos;

I get a bunch of blank records.

I must be missing something here...

McNets · Accepted Answer · 2016-12-11 17:32:23Z

9

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;

Or, if you are interested in some specific field:

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

edited Dec 11, 2016 at 17:32

answered Dec 11, 2016 at 16:17

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Trevor Turney Over a year ago

Thanks, mcNets - that's exactly what I'm looking for. Interestingly, if I run that query, straight from the table - select id, json_array_length(images->'photo-verification'->'photos') from notes.notes where images::text != '';, I get all of the IDs, but the length field is empty. I'm using a foreign data wrapper, but the data still looks okay when it comes through. Perhaps if I did a SELECT INTO, and put it in a temp table, I might have better luck. <shrug>

denarced · Accepted Answer · 2016-12-11 13:03:11Z

9

Seems casting to JSON is needed:

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

answered Dec 11, 2016 at 13:03

denarced

3723 silver badges10 bronze badges

1 Comment

Trevor Turney Over a year ago

Well, I tried select count(*) from notes where json_array_length((images->'photo-verification'->'photos')::json) > 1; and it came back with a count of zero. It does seem like that would be the right track, though. Thanks for looking!

Collectives™ on Stack Overflow

Postgres 9.5 - Querying array length of a nested JSON element

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related