Postgresql – the fastest way to verify that a json[] array is not empty

arrayjsonpostgresql

I want to verify that an json[] column the_array is not empty. For a normal array, I could check on:

the_array != '{}'

However, that does not work for a json[], as demonstrated below:

select '{}'::json[] != '{}'
ERROR:  could not identify an equality operator for type json

I could use the following check instead:

array_length(the_array, 1) != null // yes, array_length on an
                                      empty array returns null

My fear is that array_length() would iterate over the whole array to count the number of items and then return that count. In my case, I don't need the actual size of the array, I just need to know whether it is empty or not.

So, does array_length() iterate over the whole array? And if so, is there a better way to check if a json array is empty?

Best Answer

This is not returning a JSON Array,.

SELECT '{}'::json[]

The type json[] is not a "JSON Array", it's a SQL Array of type JSON. You should never be using this kind of structure. It's almost certainly a red flag of bad design.

What you want to check is that a Real JSON Array is not empty, you can do that with jsonb_array_length(jsonb). JSON is stored as JSON, or JSONB for binary json.

SELECT jsonb_array_length( '[]'::jsonb );

To see if it's empty,

SELECT jsonb_array_length( '[]'::jsonb ) = 0;

Warning, this just counts those elements, they may be null,

SELECT jsonb_array_length( '[null]'::jsonb );

So you may want to call jsonb_strip_nulls() on the array first.

Why?

The expressed requirement that artist and title must match in the same element of the JSON array is not reflected in your query, which finds all rows where at least one element matches the artist and another (possibly a different one) matches the title.

The example data for your first case was inconclusive, since the query cannot fail this way for a single array element. Your second example demonstrates the case well, though.

Solution in Postgres 9.3

There are various ways to fix this. One way would be to translate each json array element to an SQL array of composite type consisting of artist and title and match the whole type as one.

Another way would be to keep indexes like you have now (or even a single composite spanning both expressions: (json2arr(data, 'artist'), json2arr(data, 'title')). Your current query identifies possible matches. Unnest the json array data for all identified candidates and check whether both artist and title match on the same element. May or may not be efficient enough.

Better use `jsonb` in n Postgres 9.4

I am not going into detail for json / pg 9.3, because the release of jsonb in Postgres 9.4 with advanced indexing capabilities mostly obsoleted the problem. This can be implemented much simpler and more efficient with a native GIN index on a jsonb column. There are various options. To optimize for the presented case:

CREATE TABLE tracks (id serial, data jsonb);

INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect"}]');

Index:

CREATE INDEX tracks_data_gin_idx ON tracks USING gin (data jsonb_path_ops);

Query:

SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]';

Sequence of attributes in the JSON value and insignificant whitespace don't matter for jsonb. I added detailed information for jsonb to the referenced answer on SO:

Index for finding an element in a JSON array

Best Answer

Related Solutions

PostgreSQL custom operator for equality

PostgreSQL – Indexing for Attribute Combinations in JSON Array

Why?

Solution in Postgres 9.3

Better use jsonb in n Postgres 9.4

Related Question

Better use `jsonb` in n Postgres 9.4