Gabriel Fürstenheim
Input
{"a": 1, "b": 2}
Output
2
Solution
{"a": 1, "b": 2}->'b'
Input
{"a": 1, "b": 2}
Output
t
Solution
{"a": 1, "b": 2} ? 'b'
Input
{"a": 1, "b": 2}
{"a": 3, "b": 4}
Output
{"a": 4, "b": 6}
Solution
SELECT jsonb_object_agg(key, val)
FROM (
SELECT key, SUM(value::numeric) val
FROM test_table t, jsonb_each_text(info)
GROUP BY key
)s;
Input
{"a": {"c": 4}, "b": {"d": 4}}
{"a": {"c": 3}, "b": {"d": 2}}
Output
{"a": {"c": 7}, "b": {"d": 6}}
Solution
????
C solution
jsonb_deep_add(
{"a": {"c": 4}, "b": {"d": 4}},
{"a": {"c": 3}, "b": {"d": 2}}
)
{
"type": "object",
"additionalProperties": false,
"properties": {
"email": {
"type": "string"
},
"number_of_stores": {
"type": "integer"
}
}
}
Flat iteration
Recursive Iteration
Flat iteration
Input
{
"a": 1,
"b": {
"c": 2
}
}
Output
{
"a"
1
"b"
{"c": 2}
}
Recursive Iteration
Input
{
"a": 1,
"b": {
"c": 2
}
}
Output
{
"a"
1
"b"
{
"c"
2
}
}
| Numbers of rows | Level of nesting | C (ms) | SQL (ms) | Improvement |
|---|---|---|---|---|
| 2 million | 0 | 3705 | 11083 | 3x |
| 2 million | 1 | 12014 | ? | ? |
| Numbers of tweets | C (ms) | SQL (postgres json schema) (ms) | Improvement |
|---|---|---|---|
| 10000 | 8911 | 203172 | 23x |