[ ] Sqlite JSON query pattern

Sqlite JSON query pattern

Snippet

SELECT 
    id,
    json_extract(response_json, '$.usage.input_tokens') as input_tokens,
    json_extract(response_json, '$.usage.output_tokens') as output_tokens,
    json_extract(response_json, '$.usage.cache_creation_input_tokens') as cache_creation_input_tokens,
    json_extract(response_json, '$.usage.cache_read_input_tokens') as cache_read_input_tokens
FROM responses
WHERE conversation_id = '01j7jzcbxzrspg7qz9h8xbq1ww'
ORDER BY id;

README

This is a common SQL query pattern for working with JSON data stored in a SQLite database. The query extracts various fields from a JSON column called response_json using the json_extract() function. The extracted fields include:

input_tokens: The number of input tokens used in the response.
output_tokens: The number of output tokens generated in the response.
cache_creation_input_tokens: The number of input tokens used to create the cache for the response.
cache_read_input_tokens: The number of input tokens used to read the cache for the response.

The query filters the rows to only include those with a specific conversation_id and orders the results by the id column.

This pattern can be useful for analyzing usage metrics and performance characteristics of a system that generates responses based on JSON data.

irthomasthomas / undecidability