1

How to sum the values ​​of a json column filtered with regex?

Columns:

A.name: Varchar with name product

B.products: JSON with time active By product

Query:

select
    A.name as product,
    sum(((REGEXP_MATCH(B.products->>'status', 'Active:.(.*?)\"'))[1])::float) as metric
from
    tbl_accounts A
inner join tbl_products B on
    A.identifier = B.identifier
where
    B.products->>'status' like '%Active%'
group by
    A.name,
    B.products

Explanation data

JSON 1: {"times": ["Stoped: 49.05", "Active: 23.26"]}

JSON 2: {"times": ["Stoped: 59.05", "Active: 33.26"]}

Desired Output: 56.52

Output:

ERROR: could not identify an equality operator for type json
2
  • 1
    It's so much easier to answer if you provide your version of Postgres and proper table definitions (CREATE TABLE statements). And a minimal, correct data sample. Ideally, a fiddle. (Random example.) Aug 24, 2020 at 21:42
  • I will try to model a data, I have to be careful with the disclosure, corporate data Aug 24, 2020 at 22:27

1 Answer 1

1

The immediate reason for the error message is that the data type json has no equality operator. See:

You have:

...
group by
    A.name,
    B.products  -- type json!?

You can do that, using jsonb instead of json, where an equality operator is defined. But do you really want to group by B.products? (Same JSON documents?) Maybe you meant to write B.products->>'status' (Same status?) Or just GROUP BY A.name?

Aside: there may also be a simpler way to extract numbers that with REGEXP_MATCH(). You would have to define possible values B.products->>'status' and disclose the exact intention of the expression.

If you are at liberty to do so, it's typically best to store numbers in a separate key or even separate table column ....

jsonpath query in Postgres 12 or later

Your added sample values suggest you might be able to use jsonpath in Postgres 12 or later. Based on jsonb (not json).

Note: this is a proof of concept. If possible, normalize the table design and store numbers in a dedicated table column. Much simpler and more efficient.

Index

jsonpath operators can also be supported with a (default) jsonb_ops GIN index. I narrow down the scope with the expression products->'times':

CREATE INDEX products_times_gin_idx ON products USING gin ((products->'times'));

Index only helps for selective queries where not most rows have to be processed anyways!

Basic query to filter qualifying rows with jsonpath

Can use above index.

SELECT *
FROM   products B
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")';

jsonpath expression explained:

$[*] ... look at each array element of outer nesting level
? ... run the following test
(@ starts with "Active: ") ... Does element value start with 'Active:'?

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

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

See:

... and aggregate the number part

Arriving at your final query:

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;

db<>fiddle here

Related:

3
  • I liked your explanation, I'll try something with jsonb Aug 24, 2020 at 23:17
  • @LuisHenrique: You already accepted. But consider the added jsonpath solution. Aug 25, 2020 at 0:45
  • Of course, I will look, thank you very much! Aug 25, 2020 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.