1

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
}
}
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
equallyhero
  • 171
  • 1
  • 2
  • 16

2 Answers2

2

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

Ajax1234
  • 69,937
  • 8
  • 61
  • 102
1
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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Tested this & ajax124's answer, their method using the cross join is faster for my use case, applying it to about 90,000 rows the results returned in 2.47s. Using the left join method returned the same results in 70.65s. My JSON data is much denser/larger than my example which may have something to do with that. Thank you for the explanation and the time to answer. – equallyhero Sep 01 '23 at 02:25
  • If most or all rows in table `A` qualify, and not much else is in the json documents - like seems to be the case - then simply unnesting all - like Ajax' query does - will be cheaper. – Erwin Brandstetter Sep 01 '23 at 02:45