Pick random values from an array in SQL(ite)

Python has a random.choice routine in the standard library that allows you to pick a random value from an iterable. It works like this:

This will print:

I was looking for a way to quickly hydrate a table with random data in an SQLite database. To be able to do so, I needed to extract unpremeditated values from an array of predefined elements. The issue is, that SQLite doesn't support array types or have a built-in function to pick random values from an array. However, recently I came across this trick from Ricardo Ander-Egg's tweet, where he exploits SQLite's JSON support to parse an array. This idea can be further extended to pluck random values from an array.

To extract values from any JSON object in SQLite, you can use the json_extract function. Start a SQLite CLI session and run the following query:

This will give you an output as follows:

The above query parses the JSON object inside the json_extract function and extracts the last element from the greetings array. If you want to know more details about how you can extract specific elements from JSON objects, head over to the SQLite [docs on this topic].

You can pick any value from a JSON array by its index:

Now, how do we extract random elements from the above array? If we can generate a set of random indices, those can be used to access values arbitrarily from the JSON array. These random indices can be generated using SQLite's built-in random() function. The function doesn't take any arguments and generates a large positive or negative arbitrary integer. From this integer, a random index can be found by computing abs(random()) modulo n where abs(random()) denotes the absolute result of the random function and n represents the length of the target array.

For example, if the length of the array is 4, and random() produces the integer -123456789, then the index will be 123456789 % 4 = 1 :

If you run this query multiple times, you'll see that it prints a value between 0 and 3 in random order.

Similarly, if you compute abs(random()) % 5, it'll print a value between 0 to 4 and so on. Armed with this knowledge, we can extract a random value from a JSON array like this:

Running the above query will give you a single value from the JSON array in random order. Execute the query multiple times to see it in action.

Voila, we've successfully emulated Python's random.choice in SQL.

Populating a table with random data

Populating a table with randomly distributed data is useful, especially when you need to demonstrate a feature or flex your SQL fu. We can leverage the above pattern to populate a simple table with 100 data points like this:

If you run the above queries via the SQLite CLI, the final statement will reveal the stat table with the randomly filled in data: