Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

-- 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"]}


-- ... 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"


-- ...  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


-- ... 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

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"]}

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"

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