Comparing database rows before and after with SQLite JSON functions

Here's a trick I've been using to compare the rows in a table before and after I perform an operation against it. It works well for a few hundred (and maybe a few thousand) rows.

First, get hold of a copy of the primary keys (or some other unique column) in the existing table, as JSON.

select json_group_array(id) from mytable

This will return a JSON string, which you can copy and paste somewhere.

I ran it against my repos table here to select repo names, like this:

select json_group_array(full_name) from repos

Try that here.

Here's the result of that query as a very long string:

["simonw/datasette","simonw/csvs-to-sqlite","simonw/datasette-plugin-demos","simonw/datasette-cluster-map","simonw/register-of-members-interests-datasette","simonw/fivethirtyeight-datasette","simonw/global-power-plants-datasette","simonw/datasette-sql-scraper","simonw/datasette-leaflet-geojson","simonw/datasette-registry","simonw/datasette-vega","simonw/sqlite-utils","simonw/russian-ira-facebook-ads-datasette","simonw/datasette-json-html","simonw/russian-troll-tweets-datasette","simonw/datasette-render-images","simonw/datasette-small","simonw/24ways-datasette","simonw/sqlite-fts4","simonw/datasette-sqlite-fts4","simonw/db-to-sqlite","simonw/datasette-pretty-json","simonw/markdown-to-sqlite","simonw/dbf-to-sqlite","simonw/whosonfirst-datasette","simonw/datasette-car-2019","simonw/datasette-jellyfish","simonw/yaml-to-sqlite","simonw/datasette-render-html","simonw/datasette-jq","simonw/datasette-bplist","simonw/datasette-render-binary","simonw/fara-datasette","simonw/datasette-auth-github","simonw/sqlite-diffable","simonw/datasette-cors","dogsheep/dogsheep-beta","dogsheep/healthkit-to-sqlite","dogsheep/swarm-to-sqlite","dogsheep/twitter-to-sqlite","dogsheep/inaturalist-to-sqlite","dogsheep/google-takeout-to-sqlite","dogsheep/github-to-sqlite","simonw/datasette-rure","simonw/datasette-atom","dogsheep/genome-to-sqlite","dogsheep/pocket-to-sqlite","simonw/datasette-render-timestamps","dogsheep/dogsheep.github.io","simonw/museums","simonw/datasette-haversine","simonw/sqlite-transform","simonw/datasette-csvs","simonw/datasette-render-markdown","simonw/datasette-template-sql","simonw/asgi-log-to-sqlite","simonw/datasette-configure-asgi","simonw/datasette-upload-csvs","simonw/datasette-auth-existing-cookies","simonw/datasette-sentry","simonw/geojson-to-sqlite","simonw/datasette-debug-asgi","simonw/shapefile-to-sqlite","simonw/datasette-mask-columns","simonw/datasette-ics","simonw/datasette-configure-fts","simonw/fec-to-sqlite","simonw/datasette-search-all","simonw/datasette-column-inspect","simonw/covid-19-datasette","simonw/datasette-edit-schema","simonw/datasette-publish-fly","dogsheep/hacker-news-to-sqlite","simonw/datasette-show-errors","simonw/datasette-publish-vercel","simonw/big-local-datasette","simonw/datasette-clone","dogsheep/dogsheep-photos","simonw/til","simonw/datasette-media","simonw/datasette-permissions-sql","simonw/datasette-auth-tokens","simonw/datasette-psutil","simonw/datasette-plugin","simonw/datasette-plugin-template-demo","simonw/datasette-saved-queries","simonw/datasette.io","simonw/sqlite-generate","simonw/datasette-block-robots","simonw/datasette-glitch","simonw/datasette-init","simonw/datasette-write","simonw/datasette-allow-permissions-debug","simonw/sba-loans-covid-19-datasette","simonw/datasette-auth-passwords","simonw/srccon-2020-datasette","simonw/datasette-insert","simonw/datasette-copyable","simonw/datasette-insert-unsafe","simonw/datasette-graphql","simonw/parlgov-datasette","simonw/homebrew-datasette","simonw/datasette-io-redirect","simonw/datasette-schema-versions","simonw/calands-datasette","simonw/datasette-yaml","simonw/datasette-backup","simonw/sqlite-dump","simonw/datasette-dns","simonw/datasette-seaborn","simonw/sqlite-fts5-trigram","simonw/datasette-dateutil","simonw/datasette-import-table","simonw/buildpack-datasette","simonw/datasette-json-preview","dogsheep/evernote-to-sqlite","simonw/sphinx-to-sqlite","simonw/datasette-edit-templates","simonw/datasette-indieauth","simonw/datasette-ripgrep","simonw/datasette-css-properties","simonw/datasette-export-notebook","simonw/cbsa-datasette","simonw/datasette-leaflet-freedraw","simonw/datasette-leaflet","simonw/datasette-basemap","simonw/datasette-tiles","simonw/vaccinate-ca-datasette","simonw/datasette-block","simonw/us-counties-datasette","simonw/tableau-to-sqlite","simonw/django-sql-dashboard","simonw/iam-to-sqlite","simonw/azure-functions-datasette","simonw/datasette-publish-azure","simonw/datasette-placekey","simonw/datasette-remote-metadata","simonw/datasette-pyinstrument","simonw/datasette-query-links","simonw/datasette-x-forwarded-host","simonw/datasette-app","simonw/datasette-verify","simonw/datasette-plugin-template-repository","simonw/datasette-plugin-template-repository-demo-old","simonw/datasette-statistics","simonw/datasette-notebook","simonw/datasette-template-request","simonw/datasette-hello-world","simonw/datasette-jupyterlite","simonw/iam-definitions-datasette","simonw/datasette-redirect-to-https","simonw/datasette-table","simonw/datasette-hovercards","simonw/datasette-pretty-traces","simonw/datasette-tiddlywiki","simonw/google-drive-to-sqlite","simonw/datasette-redirect-forbidden","simonw/congress-legislators-datasette","simonw/datasette-hashed-urls","simonw/datasette-plugin-template-repository-demo","simonw/datasette-packages","simonw/datasette-auth0","simonw/pypi-to-sqlite","simonw/datasette-total-page-time","simonw/datasette-gzip","simonw/datasette-copy-to-memory","simonw/datasette-lite","simonw/datasette-upload-dbs","simonw/datasette-screenshots","simonw/google-calendar-to-sqlite","simonw/datasette-unsafe-expose-env","simonw/mbox-to-sqlite","simonw/datasette-socrata","simonw/datasette-low-disk-space-hook","simonw/datasette-scale-to-zero"]

Now, run the update operation - or maybe you have another table somewhere else you want to compare with, which will work fine too.

To run the comparison, construct the following query:

select id from mytable where id not in (
  select value from json_each(:previous_value)
)

Paste the JSON string (wrapped in single quotes) into the :previous_value parameter.

Here's what select value from json_each('["a","b","c"]') returns:

You can use this table in a sub-select, to return rows that now exist which did not exist before.

Try my huge query here - the SQL I ran is this:

select
  full_name
from
  repos
where
  full_name not in (
    select
      value
    from
      json_each(
        '["simonw/datasette","simonw/csvs-to-sqlite","simonw/datasette-plugin-demos","simonw/datasette-cluster-map","simonw/register-of-members-interests-datasette","simonw/fivethirtyeight-datasette","simonw/global-power-plants-datasette","simonw/datasette-sql-scraper","simonw/datasette-leaflet-geojson","simonw/datasette-registry","simonw/datasette-vega","simonw/sqlite-utils","simonw/russian-ira-facebook-ads-datasette","simonw/datasette-json-html","simonw/russian-troll-tweets-datasette","simonw/datasette-render-images","simonw/datasette-small","simonw/24ways-datasette","simonw/sqlite-fts4","simonw/datasette-sqlite-fts4","simonw/db-to-sqlite","simonw/datasette-pretty-json","simonw/markdown-to-sqlite","simonw/dbf-to-sqlite","simonw/whosonfirst-datasette","simonw/datasette-car-2019","simonw/datasette-jellyfish","simonw/yaml-to-sqlite","simonw/datasette-render-html","simonw/datasette-jq","simonw/datasette-bplist","simonw/datasette-render-binary","simonw/fara-datasette","simonw/datasette-auth-github","simonw/sqlite-diffable","simonw/datasette-cors","dogsheep/dogsheep-beta","dogsheep/healthkit-to-sqlite","dogsheep/swarm-to-sqlite","dogsheep/twitter-to-sqlite","dogsheep/inaturalist-to-sqlite","dogsheep/google-takeout-to-sqlite","dogsheep/github-to-sqlite","simonw/datasette-rure","simonw/datasette-atom","dogsheep/genome-to-sqlite","dogsheep/pocket-to-sqlite","simonw/datasette-render-timestamps","dogsheep/dogsheep.github.io","simonw/museums","simonw/datasette-haversine","simonw/sqlite-transform","simonw/datasette-csvs","simonw/datasette-render-markdown","simonw/datasette-template-sql","simonw/asgi-log-to-sqlite","simonw/datasette-configure-asgi","simonw/datasette-upload-csvs","simonw/datasette-auth-existing-cookies","simonw/datasette-sentry","simonw/geojson-to-sqlite","simonw/datasette-debug-asgi","simonw/shapefile-to-sqlite","simonw/datasette-mask-columns","simonw/datasette-ics","simonw/datasette-configure-fts","simonw/fec-to-sqlite","simonw/datasette-search-all","simonw/datasette-column-inspect","simonw/covid-19-datasette","simonw/datasette-edit-schema","simonw/datasette-publish-fly","dogsheep/hacker-news-to-sqlite","simonw/datasette-show-errors","simonw/datasette-publish-vercel","simonw/big-local-datasette","simonw/datasette-clone","dogsheep/dogsheep-photos","simonw/til","simonw/datasette-media","simonw/datasette-permissions-sql","simonw/datasette-auth-tokens","simonw/datasette-psutil","simonw/datasette-plugin","simonw/datasette-plugin-template-demo","simonw/datasette-saved-queries","simonw/datasette.io","simonw/sqlite-generate","simonw/datasette-block-robots","simonw/datasette-glitch","simonw/datasette-init","simonw/datasette-write","simonw/datasette-allow-permissions-debug","simonw/sba-loans-covid-19-datasette","simonw/datasette-auth-passwords","simonw/srccon-2020-datasette","simonw/datasette-insert","simonw/datasette-copyable","simonw/datasette-insert-unsafe","simonw/datasette-graphql","simonw/parlgov-datasette","simonw/homebrew-datasette","simonw/datasette-io-redirect","simonw/datasette-schema-versions","simonw/calands-datasette","simonw/datasette-yaml","simonw/datasette-backup","simonw/sqlite-dump","simonw/datasette-dns","simonw/datasette-seaborn","simonw/sqlite-fts5-trigram","simonw/datasette-dateutil","simonw/datasette-import-table","simonw/buildpack-datasette","simonw/datasette-json-preview","dogsheep/evernote-to-sqlite","simonw/sphinx-to-sqlite","simonw/datasette-edit-templates","simonw/datasette-indieauth","simonw/datasette-ripgrep","simonw/datasette-css-properties","simonw/datasette-export-notebook","simonw/cbsa-datasette","simonw/datasette-leaflet-freedraw","simonw/datasette-leaflet","simonw/datasette-basemap","simonw/datasette-tiles","simonw/vaccinate-ca-datasette","simonw/datasette-block","simonw/us-counties-datasette","simonw/tableau-to-sqlite","simonw/django-sql-dashboard","simonw/iam-to-sqlite","simonw/azure-functions-datasette","simonw/datasette-publish-azure","simonw/datasette-placekey","simonw/datasette-remote-metadata","simonw/datasette-pyinstrument","simonw/datasette-query-links","simonw/datasette-x-forwarded-host","simonw/datasette-app","simonw/datasette-verify","simonw/datasette-plugin-template-repository","simonw/datasette-plugin-template-repository-demo-old","simonw/datasette-statistics","simonw/datasette-notebook","simonw/datasette-template-request","simonw/datasette-hello-world","simonw/datasette-jupyterlite","simonw/iam-definitions-datasette","simonw/datasette-redirect-to-https","simonw/datasette-table","simonw/datasette-hovercards","simonw/datasette-pretty-traces","simonw/datasette-tiddlywiki","simonw/google-drive-to-sqlite","simonw/datasette-redirect-forbidden","simonw/congress-legislators-datasette","simonw/datasette-hashed-urls","simonw/datasette-plugin-template-repository-demo","simonw/datasette-packages","simonw/datasette-auth0","simonw/pypi-to-sqlite","simonw/datasette-total-page-time","simonw/datasette-gzip","simonw/datasette-copy-to-memory","simonw/datasette-lite","simonw/datasette-upload-dbs","simonw/datasette-screenshots","simonw/google-calendar-to-sqlite","simonw/datasette-unsafe-expose-env","simonw/mbox-to-sqlite","simonw/datasette-socrata","simonw/datasette-low-disk-space-hook","simonw/datasette-scale-to-zero"]'
      )
  )

Related

• sqlite Tracking SQLite table history using a JSON audit log - 2024-02-26
• sqlite Sort by number of JSON intersections - 2022-08-17
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• sqlite Seeing which functions are unique to a specific SQLite / Datasette instance - 2022-08-23
• sqlite Null case comparisons in SQLite - 2020-04-21
• sqlite List all columns in a SQLite database - 2020-05-06
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• sqlite Identifying column combination patterns in a SQLite table - 2021-01-12
• datasette Searching all columns of a table in Datasette - 2021-08-23
• jq Flattening nested JSON objects with jq - 2021-03-11

Created 2022-12-14T15:47:49-08:00 · Edit