HOWTO : Query json data in SQLite

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

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, '$') 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.

HOW TO : Configure nginx to use URI for modifying response content

That was a pretty long title for the post :). I love nginx for it’s flexibility and ease of use. It is like a swiss army knife.. can do a lot of things :).

We needed to serve some dynamic content for one of our use cases. If user visits a site using the following URL format , we want to respond with some html content that is customized using the 23456789 and 678543 strings.

A picture might help here

Here’s how this was achieved

  • Define a location section in the nginx config to respond to the URL path specified and direct it to substitute content
    location ~ "^/(?<param1>[0-9]{8})/(?<param2>[0-9]{6})" {

            root /var/www/html/test/;
            index template.html;
            sub_filter_once off;
            sub_filter '_first_param_' '$param1';
            sub_filter '_second_param_' '$param2';
            rewrite ^.*$ /template.html break;

create a file named template.html with the following content in /var/www/html/test

Breaking down the config one line at a time

location ~ "^/(?<param1>[0-9]{8})/(?<param2>[0-9]{6})" : The regex is essentially matching for the first set of digits after the / and adding that as the value for variable $param1. The first match is a series of 8 digits with each digit in the range 0-9. The second match is for a series of 6 digits with each digit in the range 0-9 and it will be added as the value for variable $param2

root /var/www/html/test/; : Specifying the root location for the location.

index template.html; : Specifying the home page for the location.

sub_filter_once off; : Specify to the sub_filter module to not stop after the first match for replacing response content. By default it processes the first match and stops.

sub_filter 'first_param' '$param1'; : Direct the sub_filter module to replace any text matching first_param in the response html with value in variable $param1.

sub_filter 'second_param' '$param2'; : Direct the sub_filter module to replace any text matching second_param in the response html with value in variable $param1.

rewrite ^.*$ /template.html break; : Specify nginx to server template.html regardless of the URI specified.

Big thanks to Igor for help with the configs!!

Overheard : Call to action

I don’t even recall where I heard this :)..  but it stuck with me for me to write it down

For somebody to do something.. three things much happen at once. The person must want do do it, they must be able to and they must be prompted to do it.

A trigger – the prompt for the action – is effective only when the person is highly motivated, or the task is very easy. If the task is hard, people end up frustrated; if they’re not motivated, they get annoyed.

HOW TO : count lines in windows command line

Say you are using netstat to checl all established network connections on a windows machine (confirmed to work on windows 7+ and windows server 2008+) and want to find out how many connections you have, you can use

netstat -an | find "ESTABLISHED" | find /v /c ""

breaking down the command string

netstat -an : Uses netstat command to display all connections and listening ports (-a) and displays them in numerical form instead of resolving DNS or using common names (-n)

| : piping (passing) output of one command to the next one

find “ESTABLISHED” : Uses find command to filter out to just lines that contain the string “ESTABLISHED”‘

find /c /v “” : exclude blank lines (/v “”) and count the number of remaining lines (/c)

If you wanted to something similar in linux, you can use

netstat -an | grep "ESTABLISHED" | wc -l

HOW TO : Query varnishlogs for requests with 404 responses

varnishlog, one of the tools provided with varnish cache, uses VSL Query Expressions ( to provide some powerful insights into the requests and responses.

Here is a how you can use varnishlog to show all client requests that are ending up with a 404 response.

sudo varnishlog -g request -i ReqURL -q "BerespStatus != 200"

Technically, this particular query shows all client requests with a response other than 200.

Breaking down the commands

-g request : shows all entries related to the request

-i ReqURL : forces varnishlog to only display the Requesting URL

-q “BerespStatus != 200” : query filter to only match non 200 responses. Note that the query has to be enclosed in “”.

HOW TO : Use awk to print values larger than certain number

Quick how to on using awk to filter results if a certain value (column) is larger than a set value.

For example, if you have a file (servers.txt) with lines in this format

a_datacenter, servers 20
 error, servers xyz
 b_datacenter, servers 21
 c_datacenter, servers 50

and you want to show only the lines that have server value larger than 20, you can do this in awk by running

grep datacenter servers.txt | awk '$3 > 20  {print ;}' | more

breaking down the commands

grep – parsing down the output to just show the lines containing datacenter

awk – $3 > 20 : Get the third variable (awk seperates text using spaces by default) and check if it is greater than 20

print – print the entire line

Project PaaS : Day 2 on Google App Engine

It looks like I was able to accomplish writing the application that I wanted to on the App Engine in 2 days!!  at least in it’s basic form.  After some help from Google, I updated the application I created yesterday ( to display the User Agent string being sent by the client.

The code has been updated to github at

Lessons from day 2?

  • Python doesn’t like tabs :). Always use spaces to ident. I was using Notepad++ as the editor and it automatically puts tabs when you hit enter. Why? Looks like that is the best practice according to this style guide (
  • The “Logs” console in the SDK toolkit should be your best friend. It let’s you know if there is any error in your code and what line it believes the error is at.

Next, I will try to pretty it up a bit.

Isn’t it amazing that I was able to create a simple app in a matter of 2 days and host it on an “infinitely” scalable  platform without even taking our my credit card.