Question 242038 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Mapping json column keys onto matching column values from 2nd table

I have a table A, where data is a json column, and timestamp is a timestamp with timezone column:

timestamp data
2023-08-29 13:00:00-04 { "a_123":{ "temp":85, "uv":5, "rain":0 }, "b_123":{ "temp":85, "uv":5, "rain":0 } }
2023-08-29 14:00:00-04 { "a_123":{ "temp":70, "uv" 1:, 1"rain":5 }, "b_123":{ "temp":73, "uv":1, "rain":7 } }
2023-08-29 15:00:00-04 { "a_123":{ "temp":83, "uv":4, "rain":1 }, "b_123":{ "temp":87, "uv":7, "rain":0 } }

I have a second table B:

id location elevation tag
a_123 04662 155m blue
b_123 84003 15m yellow

I can map individual data column keys eg. a_123 from table A onto the matching id from table B but in my case the data column keys from table A are dynamic.

How would I produce a table where the nested timestamp data is mapped to each id as follows, from the sample data provided above? Thanks for any help/docs.

id location elevation tag data
a_123 04662 155m blue {
"2023-08-29 13:00:00-04":{
"temp":85,
"uv":5,
"rain":0
},
"2023-08-29 14:00:00-04":{
"temp":70,
"uv":1,
"rain":5
},
"2023-08-29 15:00:00-04":{
"temp":83,
"uv":4,
"rain":1
}
}
b123 84003 15m yellow {
"2023-08-29 13:00:00-04":{
"temp":85,
"uv":5,
"rain":0
},
"2023-08-29 14:00:00-04":{
"temp":73,
"uv":1,
"rain":7
},
"2023-08-29 15:00:00-04":{
"temp":87,
"uv":7,
"rain":0
}
}
Tags:

2

Answers


  1. 0 
    SELECT *
FROM   b
LEFT   JOIN LATERAL (
   SELECT jsonb_object_agg(a.timestamp, a.data -> b.id)
   FROM   a
   WHERE  a.data ? b.id  -- optional, but faster
   ) a ON true

    fiddle

    1. Extract the object fields from a.data with b.id as key value using the basic json operator ->. (This way we don’t process unrelated keys that also might be there.)

    2. Build new objects with a.timestamp as key and the extracted value from step 1., and aggregate them into a nesting object in one step with json_object_agg().

    3. Do all of this in a LATERAL join. LEFT JOIN LATERAL ... ON true, to be precise. See:

      Aggregating in a lateral subquery keeps you from having to aggregate the outer table b as well, which would be unnecessary cost and complication.

    4. The operator ? in the optional WHERE a.data ? b.id excludes rows without matching top-level key early. Only works for jsonb. Uses an index efficiently – if only a small fraction of rows in table A matches.

    You said "json", but I’ll assume the more commonly used jsonb. The query works for json, too, with the respective function json_object_agg(), and without the WHERE clause.

    Login or Signup to reply.
  2. 0

    Using jsonb_each with successive cross joins:

    select t.id, t.location, t.elevation, t.tag, jsonb_object_agg(t1.timestamp, v.value)
from tableb t cross join tablea t1 cross join jsonb_each(t1.data) v
where t.id = v.key
group by t.id, t.location, t.elevation, t.tag

    See fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search