CODEWITHSUNDEEP
sqlpostgresqltimescaledb
Eu Román
Eu Román

Reputation: 323

SQL: Join tables using a value inside a JSON in Postgres

I'm using Grafana to access some PostgreSQL data (TimescaleDB). I have a 1st table called events that has one column called event, that is an object with this structure:

{"response_body": "{\"result\":[{\"profile\":{\"id\":123456}}]}"}

I also have a 2nd table called orders that has a column profile_id that corresponds to the id inside the object from the 1st table.

I want to join both tables (using the id) to access columns on the 1st table via the 2nd table, but I'm not sure how to make the INNER JOIN work:

SELECT
  event->>'url_path',
  SUM(size) AS "ORDERS SIZE (SUM)"
FROM
  events
INNER JOIN
  order ON order.profile_id = events.SOMETHING_HERE
GROUP BY
  1
LIMIT 10;

I've tried to use this on the SOMETHING_HERE part, but it doesn't work:

(event->>'response_body')::jsonb->'result'->0->'profile'->>'id'

Any ideas? Thanks a lot!

Upvotes: 1

Views: 91

Answers (1)

Zegarek
Zegarek

Reputation: 26302

It seems to work just fine if you join on that (also swapping out multiple -> for a single #>> and matching the types on both sides of =): demo

SELECT event->>'url_path' AS "URL PATH",
       SUM(size) AS "ORDERS SIZE (SUM)"
FROM events e INNER JOIN orders o ON 
o.profile_id = ((e.event->>'response_body')::jsonb #>>'{result,0,profile,id}')::bigint
GROUP BY 1
LIMIT 10;
URL PATH ORDERS SIZE (SUM)
example.com/exmpl 70773

If you need more flexibility, you can look into JSONPath.

This checks all elements in the result array, not just the first one and it also returns the value of a key with "id" in it, regardless of whether it's surrounded by asterisks or not. In your query that would be:

SELECT event->>'url_path',
       SUM(size) AS "ORDERS SIZE (SUM)"
FROM events e INNER JOIN orders o ON 
jsonb_path_match ((event->>'response_body')::jsonb
                  ,'$.result[*].profile.keyvalue() 
                    ?(@.key like_regex ".*id.*")
                    .value == $order_profile_id'
                  ,jsonb_build_object('order_profile_id',o.profile_id::int) )
GROUP BY 1
LIMIT 10;

Upvotes: 0

Related Questions