1

I have the following table:

CREATE TABLE Kittens (
  k_id INTEGER PRIMARY KEY AUTOINCREMENT,
  human_name text,
  tags json
);

With this data

INSERT INTO Kittens (human_name, tags) VALUES ("Charles",'["fiendish", "fluffy", "quiet", "discerning", "catlike"]');
INSERT INTO Kittens (human_name, tags) VALUES ("Tails",'["fluffy", "loud","catlike"]');
INSERT INTO Kittens (human_name, tags) VALUES ("Mittens",'["fluffy","regal","catlike"]');

I'd like to aggregate the common kitten tags, and I believe this requires the json_group_array function, which I've read about here:

However, this doesn't seem to be unrolling or aggregating the tags as I would expect, just list the lists consecutively.

sqlite> select json_group_array(tags) from Kittens;
["[\"fiendish\", \"fluffy\", \"quiet\", \"discerning\", \"catlike\"]","[\"fluffy\", \"loud\",\"catlike\"]","[\"fluffy\",\"regal\",\"catlike\"]"]

Any guidance on what this query should look like? Seeking something more like:

tag_array

catlike
catlike
catlike
discerning
fiendish
fluffy
fluffy
fluffy   
loud 
quiet 
regal

And ultimately, with an aggregation:

tag_array  count(*)
fluffy     3
catlike    3
fiendish   1
discerning 1
loud       1
regal      1
quiet      1
Mittenchops
  • 18,633
  • 33
  • 128
  • 246

1 Answers1

4

json_group_array() constructs an array from its elements; to extract values from an array, use json_each():

SELECT tag, count(*)
FROM (SELECT j.value AS tag
      FROM Kittens
      CROSS JOIN json_each(Kittens.tags) AS j)
GROUP BY tag;
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you. Concise and perfect. – Mittenchops Jan 18 '18 at 02:41
  • This is awesome, but a tiny bit slow. Any chance you know if there's syntax to index the json_each? CREATE INDEX IF NOT EXISTS tag_json_index ON Kittens(json_each(Kittens.tags)); ...> Error: no such function: json_each – Mittenchops Jan 18 '18 at 03:50
  • 1
    json_each() is a [table-valued function](http://www.sqlite.org/vtab.html#table_valued_functions). An index would have to be implemented by the virtual table module. – CL. Jan 18 '18 at 11:40
  • A relational database works best when you store the data relationally. – CL. Jan 18 '18 at 11:41