select distinct keys from a json column in SQLite-CodePudding

a TEXT column of a SQLite database contains json values like these one:

[{"key1":"116"},{"key2":"260"},{"key3":"123"},{"key4":"1155"},{"key4":"1507"}]

i need a DISTINCT list of json keys

key1 1
key2 1
key3 1
key4 2

can i write a VIEW using only sqlite json functions?

CodePudding user response：

You need JSON1 Extension functions like json_each() and aggregation:

SELECT j.key, COUNT(*) counter
FROM (
  SELECT j.*
  FROM tablename t, json_each(col) j
  -- WHERE ...  (if you want to apply a filter in the table's rows)
) t, json_each(t.value) j  
GROUP BY j.key;

See the demo