Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres 9.5 - Querying array length of a nested JSON element

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...

like image 808
Trevor Turney Avatar asked Dec 11 '16 11:12

Trevor Turney


People also ask

How do I query JSON data in PostgreSQL?

Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.

What is the size of JSON in PostgreSQL?

Maximum size Postgres JsonB can support As per Postgres official documentation, the maximum size is 255 MB per document.

Is Postgres Jsonb fast?

Because JSONB stores data in a binary format, queries process significantly faster. Storing data in binary form allows Postgres to access a particular JSON key-value pair without reading the entire JSON record. The reduced disk load speeds up overall performance. Support for indexing.


2 Answers

Seems casting to JSON is needed:

select json_array_length((images->'photo-verification'->'photos')::json)
from notes;
like image 72
denarced Avatar answered Oct 19 '22 04:10

denarced


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;
like image 41
McNets Avatar answered Oct 19 '22 05:10

McNets