CODEWITHSUNDEEP
sqljsonpostgresqlaggregate-functionspostgresql-9.6
ckn
ckn

Reputation: 63

How to extract value from json in Postgress based on a key pattern?

In Postgres v9.6.15, I need to get the SUM of all the values using text patterns of certain keys.

For example, having table "TABLE1" with 4 rows:

       |group  |col1
======================================
Row #1:|group1 |{ "json_key1.id1" : 1 }
Row #2:|group1 |{ "json_key1.id2" : 1 }
Row #3:|group1 |{ "json_key2.idX" : 1 }
Row #4:|group1 |{ "not_me" : 2 }

I'd like to get the int values using a pattern of the first part of the keys ( "json_key1" and "json_key2" ) and SUM them all using a CASE block like so:

SELECT table1.group as group,
  COALESCE(sum(
    CASE
    WHEN table1.col1 = 'col1_val1' THEN (table1.json_col->>'json_key1.%')::bigint
    WHEN table1.col1 = 'col1_val2' THEN (table1.json_col->>'json_key2.%')::bigint
    ELSE 0::bigint
  END), 0)::bigint AS my_result
FROM      table1 as table1
GROUP BY  table1.group;

I need "my_result" to look like:

       |group  |my_result
======================================
Row #1:|group1 |3

Is there a way to collect the values using regex or something like that. Not sure if I am checking the right documentation ( https://www.postgresql.org/docs/9.6/functions-json.html ), but I am not finding anything that can help me to achieve the above OR if that is actually possible..

Upvotes: 1

Views: 1006

Answers (1)

klin
klin

Reputation: 121604

Use jsonb_each_text() in a lateral join to get pairs (key, value) from json objects:

select 
    group_col as "group",
    coalesce(sum(case when key like 'json_k%' then value::numeric end), 0) as my_result
from table1
cross join jsonb_each_text(col1)
group by group_col

Db<>fiddle.

Upvotes: 1

Related Questions