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!!

HOW TO : Configure nginx for WordPress permalinks

Over the last week, I moved this blog from a LAMP (Linux, Apache, MySQL, PHP) stack to LEMP (Linux, Nginx, MySQL, PHP) stack. Have a blog post in the works with all the gory details, but wanted to quick document a quirk in the WordPress + Nginx combination that broke permalinks on this site.

Permalinks are user friendly permanent static URLs for a blog post. So for example this particular blog post’ URL is…press-permalinks/

instead of

This works by default in Apache because WordPress puts in the required rewrite rules.

To get it work in Nginx, you have to add the following config in the Nginx site configuration

Under the / location context, add the following

try_files $uri $uri/ /index.php?$args;

This is essentially telling Nginx to try to display the URI as is, and if it fails that, pass the URI as an argument to index.php.

HOW TO : Query varnishlogs to show backend server

Command parameters for varnishlog to view the backend server that is processing the request. In this particular case, I wanted to see the request URL and backend server for any responses with HTTP code 401 (unauthorized access)

sudo varnishlog -i BackendOpen,BereqURL -q "BerespStatus == 401"

HOW TO : Search which package contains a filename

If you are using a Linux system that uses yum for package management (like Fedora, Centos, RHEL), you can use the following command to find out which package contains a file. This is useful when you want to figure out which package to install. For example, dig (DNS utility) doesn’t come pre-installed on the system. And running “sudo yum install dig” doesn’t do anything.

sudo yum whatprovides '*/dig'

This returns

Loaded plugins: fastestmirror
 Loading mirror speeds from cached hostfile
 32:bind-utils-9.8.2-0.47.rc1.el6.x86_64 : Utilities for querying DNS name servers
 Repo : base
 Matched from:
 Filename : /usr/bin/dig

breaking down the command options

whatprovides : Is used to find out which package provides some feature or file. Just use a specific name or a file-glob-syntax wildcards to list the packages available or installed that provide that feature or file.

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 : Use grep to search for content at end of line

If you want to search for a pattern at the end of a line, you can use

tail -f logfile | grep -v "0$"

breaking down the commands

tail -f : standard tail command. Continuous output to console as the file grows (or until it ends)

grep -v : -v command forces grep to show content that doesn’t match pattern

0$ : This regex is specifically looking for a 0 at the end of the line, which is denoted by $.