I have SQLite table with columns like this:
id | info |
---|---|
1 | [{“a”: 1, “b”:2}, {“a”: 3, “b”:4}] |
2 | [{“a”: 5, “b”:6}, {“a”: 7, “b”:8}, {“a”: 9, “b”:10},] |
I need to get table like this:
id | sum(a*b) |
---|---|
1 | 14 |
2 | 176 |
My best attempt looks like this, I found it in another topic
SELECT t1.id, sum((t2.value->>'$.a')*(t2.value->>'$.b'))
FROM demo AS t1
JOIN json_each((SELECT info FROM demo WHERE id = t1.id)) AS t2
but this query gives error “malformed JSON”. I also tried to do it without join but result was the same error.
I don’t understand, why they’re malformed JSON if I do aggregation? there should be more subqueries?
How can I fix it?
user25625961 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.