out

Expanded display is on.
DROP TABLE
DROP FUNCTION
DROP FUNCTION
psql:hearthstone.sql:5: ERROR:  index "card_mechanics_size" does not exist
psql:hearthstone.sql:6: ERROR:  index "card_mechanics_size_fail" does not exist
BEGIN
CREATE TABLE
COPY 1
CREATE TABLE
INSERT 0 1287
COMMIT
-[ RECORD 1 ]---------------------------------------------------------------------
QUERY PLAN | Limit  (cost=0.00..0.92 rows=10 width=32)
-[ RECORD 2 ]---------------------------------------------------------------------
QUERY PLAN |   ->  Seq Scan on cards  (cost=0.00..135.06 rows=1462 width=32)
-[ RECORD 3 ]---------------------------------------------------------------------
QUERY PLAN |         Filter: (jsonb_array_length((data -> 'mechanics'::text)) > 0)

CREATE INDEX
-[ RECORD 1 ]---------------------------------------------------------------------
QUERY PLAN | Limit  (cost=0.00..1.76 rows=10 width=32)
-[ RECORD 2 ]---------------------------------------------------------------------
QUERY PLAN |   ->  Seq Scan on cards  (cost=0.00..75.67 rows=429 width=32)
-[ RECORD 3 ]---------------------------------------------------------------------
QUERY PLAN |         Filter: (jsonb_array_length((data -> 'mechanics'::text)) > 0)

CREATE FUNCTION
CREATE FUNCTION
CREATE INDEX
-[ RECORD 1 ]---------------------
?column?  | "Acidic Swamp Ooze"
mechanics | ["Battlecry"]
-[ RECORD 2 ]---------------------
?column?  | "Ancestral Infusion"
mechanics | ["Taunt"]
-[ RECORD 3 ]---------------------
?column?  | "Arcane Missiles"
mechanics | ["ImmuneToSpellpower"]
-[ RECORD 4 ]---------------------
?column?  | "Archmage"
mechanics | ["Spellpower"]
-[ RECORD 5 ]---------------------
?column?  | "Bloodlust"
mechanics | ["OneTurnEffect"]
-[ RECORD 6 ]---------------------
?column?  | "Bluegill Warrior"
mechanics | ["Charge"]
-[ RECORD 7 ]---------------------
?column?  | "Booty Bay Bodyguard"
mechanics | ["Taunt"]
-[ RECORD 8 ]---------------------
?column?  | "Claw"
mechanics | ["OneTurnEffect"]
-[ RECORD 9 ]---------------------
?column?  | "Claws"
mechanics | ["OneTurnEffect"]
-[ RECORD 10 ]--------------------
?column?  | "Dalaran Mage"
mechanics | ["Spellpower"]

-[ RECORD 1 ]------------------------------------------------------------------------------------------
QUERY PLAN | Limit  (cost=0.54..5.70 rows=10 width=32)
-[ RECORD 2 ]------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Index Scan using card_mechanics_size on cards  (cost=0.54..222.19 rows=429 width=32)
-[ RECORD 3 ]------------------------------------------------------------------------------------------
QUERY PLAN |         Index Cond: (json_length(((data -> 'mechanics'::text))::json) > 0)

script.sql

\x
drop table cards;
drop function json_length(json);
drop function json_length(jsonb);
drop index card_mechanics_size;
drop index card_mechanics_size_fail;


begin;
        create temporary table card_data(
               data jsonb
        );

        copy card_data(data) from '/home/omarqureshi/AllSets.json' csv quote E'\x01' delimiter E'\x02';

        create table cards(
               type              text,
               data              jsonb
        );

        insert into cards
               select key as type,
                      jsonb_array_elements(value) as data from jsonb_each((select data from card_data));
commit;

-- uses sequential scan
-- select data->'name', data->'mechanics' as mechanics from cards where jsonb_array_length(data->'mechanics') > 0 limit 10;
explain select data->'name', data->'mechanics' as mechanics from cards where jsonb_array_length(data->'mechanics') > 0 limit 10;


-- bug with the query planner in 9.4.1 ?

create index card_mechanics_size_fail on cards(jsonb_array_length(data->'mechanics'));
explain select data->'name', data->'mechanics' as mechanics from cards where jsonb_array_length(data->'mechanics') > 0 limit 10;


-- index scan

create or replace function json_length(data json) returns integer as $$
       return data.length;
$$ language plv8 immutable strict;

create or replace function json_length(data jsonb) returns integer as $$
       select json_length(data::json);
$$ language sql;


create index card_mechanics_size on cards(json_length(data->'mechanics'));


select data->'name', data->'mechanics' as mechanics from cards where json_length(data->'mechanics') > 0 limit 10;
explain select data->'name', data->'mechanics' as mechanics from cards where json_length(data->'mechanics') > 0 limit 10;