{
  "$type": "site.standard.document",
  "canonicalUrl": "https://rednafi.com/system/random-choice-in-sqlite/",
  "description": "Emulate Python's random.choice in SQLite using JSON arrays and the random() function. Populate tables with realistic test data efficiently.",
  "path": "/system/random-choice-in-sqlite/",
  "publishedAt": "2022-09-02T00:00:00.000Z",
  "site": "at://did:plc:fgtm2c26vfcj74rfmeggbyqj/site.standard.publication/3mnl6f7ob462z",
  "tags": [
    "Database",
    "SQL",
    "Data Structures"
  ],
  "textContent": "Python has a random.choice routine in the standard library that allows you to pick a\nrandom value from an iterable. It works like this:\n\nThis will print:\n\nI was looking for a way to quickly hydrate a table with random data in an SQLite database.\nTo be able to do so, I needed to extract unpremeditated values from an array of predefined\nelements. The issue is, that SQLite doesn't support array types or have a built-in function\nto pick random values from an array. However, recently I came across this [trick from\nRicardo Ander-Egg's tweet], where he exploits SQLite's JSON support to parse an array. This\nidea can be further extended to pluck random values from an array.\n\nTo extract values from any JSON object in SQLite, you can use the json_extract function.\nStart a SQLite CLI session and run the following query:\n\nThis will give you an output as follows:\n\nThe above query parses the JSON object inside the json_extract function and extracts the\nlast element from the greetings array. If you want to know more details about how you can\nextract specific elements from JSON objects, head over to the SQLite [docs on this topic].\n\nYou can pick any value from a JSON array by its index:\n\nNow, how do we extract random elements from the above array? If we can generate a set of\nrandom indices, those can be used to access values arbitrarily from the JSON array. These\nrandom indices can be generated using SQLite's built-in random() function. The function\ndoesn't take any arguments and generates a large positive or negative arbitrary integer.\nFrom this integer, a random index can be found by computing abs(random()) modulo n where\nabs(random()) denotes the absolute result of the random function and n represents the\nlength of the target array.\n\nFor example, if the length of the array is 4, and random() produces the integer\n-123456789, then the index will be 123456789 % 4 = 1 :\n\nIf you run this query multiple times, you'll see that it prints a value between 0 and 3\nin random order.\n\nSimilarly, if you compute abs(random()) % 5, it'll print a value between 0 to 4 and so\non. Armed with this knowledge, we can extract a random value from a JSON array like this:\n\nRunning the above query will give you a single value from the JSON array in random order.\nExecute the query multiple times to see it in action.\n\nVoila, we've successfully emulated Python's random.choice in SQL.\n\nPopulating a table with random data\n\nPopulating a table with randomly distributed data is useful, especially when you need to\ndemonstrate a feature or flex your SQL fu. We can leverage the above pattern to populate a\nsimple table with 100 data points like this:\n\nIf you run the above queries via the SQLite CLI, the final statement will reveal the stat\ntable with the randomly filled in data:\n\n\n\n\n[trick from ricardo ander-egg's tweet]:\n    https://twitter.com/ricardoanderegg/status/1564723221173342220?s=20&t=V4TtJsxqyH0IuheqhEvb4w\n\n\n[on this topic]:\n    https://www.sqlite.org/json1.html#jex",
  "title": "Pick random values from an array in SQL(ite)"
}