CODEWITHSUNDEEP
sqlpostgresqljsonb
Jakub Czaplicki
Jakub Czaplicki

Reputation: 1847

Postgresql query dictionary of objects in JSONB field

I have a table in a PostgreSQL 9.5 database with a JSONB field that contains a dictionary in the following form:

{'1': {'id': 1,
       'length': 24,
       'date_started': '2015-08-25'},
 '2': {'id': 2,
       'length': 27,
       'date_started': '2015-09-18'},
 '3': {'id': 3,
       'length': 27,
       'date_started': '2015-10-15'},
}

The number of elements in the dictionary (the '1', '2', etc.) may vary between rows.

I would like to be able to get the average of length using a single SQL query. Any suggestions on how to achieve this ?

Upvotes: 0

Views: 3381

Answers (1)

Marth
Marth

Reputation: 24812

Use jsonb_each:

[local] #= SELECT json, AVG((v->>'length')::int)
           FROM j, jsonb_each(json) js(k, v)
           GROUP BY json;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────────────┐
│                                                                                           json                                                                                        │         avg         │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────┤
│ {"1": {"id": 1, "length": 240, "date_started": "2015-08-25"}, "2": {"id": 2, "length": 27, "date_started": "2015-09-18"}, "3": {"id": 3, "length": 27, "date_started": "2015-10-15"}} │ 98.0000000000000000 │
│ {"1": {"id": 1, "length": 24, "date_started": "2015-08-25"}, "2": {"id": 2, "length": 27, "date_started": "2015-09-18"}, "3": {"id": 3, "length": 27, "date_started": "2015-10-15"}}  │ 26.0000000000000000 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┘
(2 rows)

Time: 0,596 ms

Upvotes: 1

Related Questions