![]() This makes the code a whole lot eaiser to understand and to maintain.Assume I have a sqlite table features which has a column data that contains json objects. to store intermediate results and write a sequence of SQL statements instead. Consider using SQLite in memory tables - CREATE TEMP TABLE. It is easy to waste your time writing extremely convoluted and hard to maintain SQL in an effort to perform in-place JSON manipulation. Having now worked with JSON1 in SQLite for a while I have a tip to share with others going down the same road. 1 Answer Sorted by: 4 Use jsonextract () again after jsoneach (jsonextract ()) to extract each x and y and aggregate: SELECT f.id, MIN (jsonextract (value, '.x')) x, MIN (jsonextract (value, '.y')) y FROM features f, jsoneach (jsonextract (f.data, '.A.B.coordinates')) GROUP BY f.id See the demo. The WHERE id = bits ensure that we target the right row.If the relevant value does not exsit in the first place we do not want to do a + 1 on a null value so we do an IFNULL TEST.The SELECT json_set part is where we establish the value to be updated. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |