Patterns to query message history

[nzoschke]

I've started to save all the json messages in logs.

What are good patterns to query this to answer questions like:

• what tracks were played in the previous session
  • on air only
  • remove dupes
• when did different chorus phrases come on air

I have some "jq" scripts that begin to answer some of this.

I would also like to explore using full blown SQL with duckdb or Postgres jsonb.

Then we can build additional tools for analysis, library management and building visual shows on top of the data.

[nzoschke]

@hfanieng can you share any queries?

[nzoschke]

Tangentially related I'm wondering if some of the player messages should include

{"source": {"id": 123, "player": 1, "slot": "SLOT"}}

As this would make it easier to "join" back to a track. Without this you need to go "find" the previous track event for the given player.

[hfanieng]

@hfanieng can you share any queries?

INSERT INTO playlist (track_id, track_artist, track_bpm, track_device, track_genre, track_key, track_label, track_title, track_timestamp, rekordbox_id) VALUES
(1, 'Depeche Mode', 93.73, 'XDJ-XZ', NULL, NULL, NULL, 'A Question Of Lust', '2024-07-21 09:44:42', 7733),
(2, 'Depeche Mode', 113.7, 'XDJ-XZ', NULL, NULL, NULL, 'Everthing Counts', '2024-07-21 09:48:25', 7735),
(3, 'Depeche Mode', 119.99, 'XDJ-XZ', NULL, NULL, 'Columbia/Mute', 'Broken', '2024-07-21 09:57:17', 7737),
(4, 'Depeche Mode', 127.87, 'XDJ-XZ', NULL, NULL, NULL, 'I Feel Loved', '2024-07-21 10:01:06', 7738),
(5, 'Depeche Mode', 154.99, 'XDJ-XZ', NULL, NULL, NULL, 'Photographic (Live @ Hammersmith Odeon 1982)', '2024-07-21 10:05:28', 7739);

do you mean something like this?