clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805515 fiddles created (40736 in the last week).

CREATE TABLE accounts (identifier int, name varchar); INSERT INTO accounts VALUES (1, 'foo') , (2, 'bar') , (3, 'baz') ; CREATE TABLE products (identifier int, products jsonb); INSERT INTO products VALUES (1, '{"times": ["Stoped: 49.05", "Active: 23.26"]}') , (1, '{"times": ["Stoped: 49.05", "Active: 10", "Active: 100"]}') , (2, '{"times": ["Stoped: 59.05", "Active: 33.26"]}') , (3, '{"times": ["Stoped: 59.05", "Broken: 66.66"]}') ; -- expression index with default jsonb_ops operator class (!) CREATE INDEX products_times_gin_idx ON products USING gin ((products->'times'));
3 rows affected
4 rows affected
 hidden batch(es)


-- filter rows with jsonpath (can use index) SELECT * FROM products B WHERE B.products->'times' @? '$[*] ? (@ starts with "Active: ")';
identifier products
1 {"times": ["Stoped: 49.05", "Active: 23.26"]}
1 {"times": ["Stoped: 49.05", "Active: 10", "Active: 100"]}
2 {"times": ["Stoped: 59.05", "Active: 33.26"]}
 hidden batch(es)


-- ... unnest and return only qualifying JSON array elements SELECT * FROM accounts A JOIN products B USING (identifier) , jsonb_path_query(B.products->'times', '$[*] ? (@ starts with "Active: ")') act WHERE B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx ;
identifier name products act
1 foo {"times": ["Stoped: 49.05", "Active: 10", "Active: 100"]} "Active: 10"
1 foo {"times": ["Stoped: 49.05", "Active: 10", "Active: 100"]} "Active: 100"
1 foo {"times": ["Stoped: 49.05", "Active: 23.26"]} "Active: 23.26"
2 bar {"times": ["Stoped: 59.05", "Active: 33.26"]} "Active: 33.26"
 hidden batch(es)


-- ... get results as text SELECT * FROM accounts A JOIN products B USING (identifier) , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act WHERE B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx ;
identifier name products value
1 foo {"times": ["Stoped: 49.05", "Active: 10", "Active: 100"]} Active: 10
1 foo {"times": ["Stoped: 49.05", "Active: 10", "Active: 100"]} Active: 100
1 foo {"times": ["Stoped: 49.05", "Active: 23.26"]} Active: 23.26
2 bar {"times": ["Stoped: 59.05", "Active: 33.26"]} Active: 33.26
 hidden batch(es)


-- ... and aggregate the number part SELECT A.name as product, sum(right(act::text, -8)::float) -- -8 = length('Active: ') FROM accounts A JOIN products B USING (identifier) , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act WHERE B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx GROUP BY 1;
product sum
bar 33.26
foo 133.26
 hidden batch(es)