Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl(id text, step_data jsonb);
INSERT INTO tbl VALUES 
('a1', '{
  "events": [
    {
      "timestamp": "2021-04-07T17:46:13.739Z",
      "meta": [
        {"key": "length", "value": "0.898"},
        {"key": "height", "value": "607023104"},
        {"key": "weight", "value": "33509376"}
      ]
    },
    {
      "timestamp": "2021-04-07T17:46:13.781Z",
      "meta": [
        {"key": "color", "value": "0.007"},
        {"key": "count", "value": "641511424"},
        {"key": "age", "value": "0"}
      ]
    }
  ]
}')
-- second row with 2x length and no color:
, ('a2', '{
  "events": [
    {
      "timestamp": "2021-04-07T17:46:13.739Z",
      "meta": [
        {"key": "length", "value": "0.898"},
        {"key": "height", "value": "607023104"},
        {"key": "weight", "value": "33509376"}
      ]
    },
    {
      "timestamp": "2021-04-07T17:46:13.781Z",
      "meta": [
        {"key": "length", "value": "666.666"},
        {"key": "count", "value": "641511424"},
        {"key": "age", "value": "0"}
      ]
    }
  ]
}')

CREATE TABLE



INSERT 0 2


-- crosstab query chosing the *greatest* value from duplicates
SELECT *
FROM   crosstab(
   $$
   SELECT id, metadata->>'key', metadata ->>'value'
   FROM  (SELECT id, jsonb_array_elements(jsonb_array_elements(step_data -> 'events') -> 'meta') metadata FROM tbl) AS parsed_keys
   ORDER  BY 1, 2, 3
   $$
  , $$VALUES ('length'), ('color'), ('weight')$$
   ) AS ct (id text, length float, color float, weight float);


id
length
color
weight


a1
0.898
0.007
33509376

a2
666.666
null
33509376



SELECT 2


-- crosstab query chosing the *smallest* value from duplicates
SELECT *
FROM   crosstab(
   $$
   SELECT id, metadata->>'key', metadata ->>'value'
   FROM  (SELECT id, jsonb_array_elements(jsonb_array_elements(step_data -> 'events') -> 'meta') metadata FROM tbl) AS parsed_keys
   ORDER  BY 1, 2, 3 DESC
   $$
 , $$VALUES ('length'), ('color'), ('weight')$$
   ) AS ct (id text, length float, color float, weight float);


id
length
color
weight


a1
0.898
0.007
33509376

a2
0.898
null
33509376



SELECT 2


-- aggregate FILTER
SELECT id
     , min(val) FILTER (WHERE key = 'length') AS length
     , min(val) FILTER (WHERE key = 'color') AS color
     , min(val) FILTER (WHERE key = 'weight') AS weight
FROM (
   SELECT id, metadata->>'key' As key, (metadata ->>'value')::float AS val
   FROM  (SELECT id, jsonb_array_elements(jsonb_array_elements(step_data -> 'events') -> 'meta') metadata FROM tbl) AS parsed_keys
   ) sub
GROUP  BY id;


id
length
color
weight


a1
0.898
0.007
33509376

a2
0.898
null
33509376



SELECT 2


-- a_horse's SQL/JSON query:
select t.id, 
       jsonb_path_query_first(t.step_data, '$.events[*].meta[*] ? (@.key == "length").value') #>> '{}' as length,
       jsonb_path_query_first(t.step_data, '$.events[*].meta[*] ? (@.key == "color").value') #>> '{}' as color,
       jsonb_path_query_first(t.step_data, '$.events[*].meta[*] ? (@.key == "weight").value') #>> '{}' as weight
from tbl t;


id
length
color
weight


a1
0.898
0.007
33509376

a2
0.898
null
33509376



SELECT 2


-- nachospiu's conditional aggregate
SELECT id,
  MAX(CASE WHEN metadata->>'key' = 'length' THEN  metadata->>'value' END) AS length,
  MAX(CASE WHEN metadata->>'key' = 'color' THEN  metadata->>'value' END) AS color,
  MAX(CASE WHEN metadata->>'key' = 'weight' THEN  metadata->>'value' END) AS weight
FROM (SELECT id, jsonb_array_elements(jsonb_array_elements(step_data #> '{events}') #> '{meta}') as metadata 
      FROM tbl t) AS aux
GROUP BY id;


id
length
color
weight


a1
0.898
0.007
33509376

a2
666.666
null
33509376



SELECT 2