I have a column in my db entitled supers with a JSON element inside.

Below is its structure:

[
  {
    "name": "Trample",
    "category": "evergreen"
  },
  {
    "name": "Spell",
    "category": "keyword"
  },
  {
    "name": "Trample token",
    "category": "keyword"
  },
  {
    "name": "Cast",
    "category": "keyword"
  },
  {
    "name": "Cost",
    "category": "keyword"
  },
  {
    "name": "Total",
    "category": "keyword"
  },
  {
    "name": "Power",
    "category": "keyword"
  },
  {
    "name": "Creature token",
    "category": "keyword"
  },
  {
    "name": "Control",
    "category": "keyword"
  },
  {
    "name": "Less",
    "category": "keyword"
  },
  {
    "name": "Elder",
    "category": "cardType"
  },
  {
    "name": "Dinosaur",
    "category": "cardType"
  },
  {
    "name": "Creature",
    "category": "cardType"
  },
  {
    "name": "Legendary",
    "category": "cardType"
  },
  {
    "name": "Cost x Less",
    "category": "super"
  },
]

I have the following query to get all rows that have similar values:

select name 
from all_cards 
where exists (select 1 
              from jsonb_array_elements(supers) f(x) 
              where x->>'category' = 'keyword' 
                and x->>'name' in ('Spell', 'Trample token', 'Cast', 'Cost', 'Total', 'Power', 'Creature token', 'Control', 'Less') 
              having count(*)>=8);

This query works to get all the names where count(*) >=8 but how do I get that count(*) out as a value, so I can sort the results based on that number?

I've tried jsonb_array_length, count, sum, nothing seems to be able to give me the answer I'm looking for.

CodePudding user response：

You can do a cross join to get the count:

select ac.name, k.num_keywords
from all_cards  ac
  cross join lateral (
    select count(*) as num_keywords
    from jsonb_array_elements(ac.supers) f(x) 
    where x->>'category' = 'keyword' 
      and x->>'name' in ('Spell', 'Trample token', 'Cast', 'Cost', 'Total', 'Power', 'Creature token', 'Control', 'Less')  
  ) k
where k.num_keywords >= 8