json

A self note for querying json data in SQLite. BTW, I think SQLite is an under utilized and under appreciated swiss army tool for data storage and manipulation. And thanks to Richard Hipp, it is free.

If you have a column defined as a json type in your SQLite database, quickest way to search for the data is json_extract. A full set of functions available are documented at https://www.sqlite.org/json1.html

If you have a column named family_details in a table family with the following json in it as an example

{
	"father": {
		"name": "dad",
		"birthday": "1/1/2000",
		"pet_name": "daddy"
	},
	"mother": {
		"name": "mom",
		"birthday": "1/1/2001",
		"pet_name": "mommy"
	},
	"sons": [
		{
			"name": "son_one",
			"birthday": "1/2/2020",
			"pet_name": "sonny_one"
		},
		{
			"name": "son_two",
			"birthday": "1/2/2021",
			"pet_name": "sonny_two"
		}
	],
	"daughters": [
		{
			"name": "princess_one",
			"birthday": "1/2/2020",
			"pet_name": "princy_one"
		},
		{
			"name": "princess_two",
			"birthday": "1/2/2021",
			"pet_name": "princy_two"
		}
	]
}

and you want to print the name of the father, you can use

select json_extract(family_details, '$.father.name') as father_name
from family

json_extract uses the name of the column and the json node as parameters. In this case, we used $(which denotes the root), father and name (under father) as the json node.