Postgresql – Duplicate results from query trying to find cross-indexed multiple tables

join;postgresql

I working with a database of several hundred schemas with 250 tables and thousands of records. PGAdmin III 1.22 as my only tool. A GUI interface to the database is not robustly written and occasionally cross-indexes some entries. I am trying to write a query that shows a list of the cross-indexed items and display their LABEL to the users rather than the ID. This allows the user to use the GUI program to fix the issue with out requiring postgresql knowledge.

Here's the query at this point:

SET search_path="SCHEMA_NAME";
SELECT 
  e1.label,
  c1.label,
  h1.contentsid,
  h1.group
FROM
  hostdata as h1
INNER JOIN hostdata as h2
  on h1.group = h2.group
  and h1.contentsid = h2.contentsid
  and h1.id != h2.id
INNER JOIN class as c1
  on t1.classid = c1.id
INNER JOIN element as e1
  on t1.elementid = e1.id
ORDER BY
  t1.group ASC,
  t1.contentsid ASC,
  c1.label ASC,
  e1.label ASC;

The cross-indexing occurs when the GROUP and CONTENTSID fields are both duplicated in two or more records in the HOSTDATA table. It's OK to have one duplicated as long as both fields are not the same. If it matters, the GROUP field is text.

What I am trying to do is compare the records in HOSTDATA to each other, list those that match another record in the GROUP and CONTENTSID fields, and then list them along with the LABELs from the CLASS and ELEMENT tables.

The above query works perfectly as long as there are only two matches, but as soon as the number of matches exceeds that the results multiply – 8 matches results in 7 returns per record – one for each match – 8 times 7 instead of just a list of 8. This is obviously because the first INNER JOIN returns multiple true conditions.

Performance is not an issue. I haven't yet come up with a way to re-arrange the sort and query to return the desired truncated output.

Best Answer

Never mind. I re-wrote it and this seems to work;

SET search_path="SCHEMA_NAME";
SELECT e1.label, c1.label, h1.contentsid, h1.group
FROM hostdata as h1
INNER JOIN (
    SELECT contentsid, group
    FROM hostdata 
        GROUP BY contentsid, group
    HAVING COUNT(id) > 1
    ) dup 
    ON h1.group = dup.group AND h1.contentsid = dup.contentsid
INNER JOIN class as c1
  on h1.classid = c1.id
INNER JOIN element as e1
  on h1.elementid = e1.id
ORDER BY t1.group, t1.contentsid;

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

Limiting the Number of Records Joined

Postgresql – Index for finding combinations of attributes 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