json_paths.sql

CREATE TABLE accounts(address text NOT NULL);
INSERT INTO accounts(address) SELECT md5(RANDOM()::TEXT) FROM generate_series(1, 500000);

CREATE TABLE balances(claimants JSONB NOT NULL);
CREATE INDEX balances_gin_idx ON balances USING gin(claimants jsonb_path_ops);

-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;

-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;

-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;

-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;

-- insert 500k balances, one balance per account
INSERT INTO balances(claimants)
SELECT json_build_array(json_build_object('destination',a.address,'predicate', md5(RANDOM()::TEXT))) FROM accounts as a;

-- RUN THE FOLLOWING SQL IN THE QUERY BUILDER ON THE RIGHT
--
-- get a random destination with the following command

SELECT json_build_array(json_build_object('destination',a.address)) FROM accounts as a ORDER BY RANDOM () LIMIT 1

-- replace the query below with the result from above
-- pick on destination from the list above and replace in the following query

SELECT * FROM balances WHERE claimants @> '[{"destination" : "751c2aa04ba42311556fae785fe4a7c9"}]';