Complex query with JSON array in PostgreSQL

Question

I've got a view in my Postgres DB with word/clue pairs for puzzles and puzzle IDs, called "allwords":

    word  |  clue       |  puz_id
    =============================
    dog   |  animal     |  1
    -----------------------------
    cat   |  animal     |  1
    -----------------------------
    apple |  fruit      |  2
    -----------------------------
    etc...

Now I want to see the matches in the DB against an array of word/clue pairs passed in a JSON string, which may look like

[{"word": "dog", "clue": "%animal%"}, {"word": "cat", "clue": "%ani%"}, {"word": "appl%", "clue": "fruit"}]

I use jsonb_to_recordset in PostgreSQL to convert the JSON into a table, then my final query looks as follows:

select a."word", a."clue", count(a.puz_id) as "matched"
from allwords a
where exists (
    select 1 from 
    jsonb_to_recordset('[{"word": "dog", "clue": "%animal%"}, {"word": "cat", "clue": "%ani%"}, {"word": "appl%", "clue": "fruit"}]'::jsonb) 
    as jsdata("word" text, clue text)
    where a."word" ilike jsdata."word" and a."clue" ilike jsdata.clue
    )
group by a."word", a."clue"
order by "matched" desc;

The result I get now is grouped by the matching words/clues from the DB table:

    word  |  clue       |  matched
    =============================
    dog   |  animal     |  5 (e.g.)
    -----------------------------
    cat   |  animal     |  3 (e.g.)
    -----------------------------
    apple |  fruit      |  1 (e.g.)
    -----------------------------

The question is: how can I get a similar match table grouped by both matching and matched word/clue pairs? I expect something like:

    matched_word  |  matched_clue      | matching_word  |  matching_clue       |  matched
    =======================================================================================
    dog           |  %animal%          | dog            |  animal              |  5
    ---------------------------------------------------------------------------------------
    cat           |  %ani%             | cat            |  animal              |  3
    ---------------------------------------------------------------------------------------
    appl%         |  fruit             | apple          |  fruit               |  1
    ---------------------------------------------------------------------------------------

s0mbre · Accepted Answer · 2021-04-22 01:00:46Z

1

Got that! Query should use join on ilike:

select jsdata."word", jsdata.clue, count(a.puz_id) as "matched" from 
allwords a
join 
    jsonb_to_recordset('[{"word": "dog", "clue": "%animal%"}, {"word": "cat", "clue": "%ani%"}, {"word": "appl%", "clue": "fruit"}]'::jsonb) 
    as jsdata("word" text, clue text)
    on (a."word" ilike jsdata."word" and a.clue ilike jsdata.clue) -- THIS WORKS!!!
group by jsdata."word", jsdata.clue
order by "matched" desc;

answered Apr 22, 2021 at 1:00

s0mbre

5848 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Complex query with JSON array in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related