Category: PostgreSQL

Query nested arrays in PostgreSQL JSON data

Posted on by rchapin

The following is an example showing how to query multiple nested arrays in JSON data in a PostgreSQL database.

Given

CREATE TABLE sample_json (
  id serial PRIMARY KEY,
  name varchar(64),
  json_data json
);


INSERT INTO sample_json (name, json_data)
VALUES
	(
	'NA',
	'
	{
	    "location": "US",
	    "topLevelArray": [
	        {
	            "id": 1,
	            "secondLevelArray": [
	                {
	                    "key": "someKey",
	                    "operator": "=",
	                    "value": 10
	                },
	                {
	                    "key": "foo",
	                    "operator": ">=",
	                    "value": 5
	                },
	                {
	                    "key": "someOtherKey",
	                    "operator": ">",
	                    "value": 647
	                }
	            ]
	        },
	        {
	            "id": 2,
	            "secondLevelArray": [
	                {
	                    "key": "blah",
	                    "operator": "<",
	                    "value": 7
	                }
	            ]
	        }
	    ]
	}
	'
	),
	(
	'EU',
	'
	{
	    "location": "poland",
	    "topLevelArray": [
	        {
	            "id": 2,
	            "secondLevelArray": [
	                {
	                    "key": "bar",
	                    "operator": "<",
	                    "value": 10
	                },
	                {
	                    "key": "moo",
	                    "operator": ">=",
	                    "value": 16
	                },
	                {
	                    "key": "baz",
	                    "operator": "!=",
	                    "value": 9
	                }
	            ]
	        }
	    ]
	}
	'	
	)
;

With the aforementioned data, let’s say we want to know the id of the rows that have an object in the secondLevelArray with operator equal to >= and the value of the key field.

The concept to understand to be able to search for all of the keys in the secondLevelArray where the operator is >= is the lateral join. The TLDR; is that a subquery appearing in the FROM clause can reference columns provided by preceding items. Or, you can write clauses in the FROM clause that read from the result of previous FROM clauses.

Let’s run some queries and go through them, line-by-line. First we will just select everything in the table.

SELECT * FROM sample_json;

|id |name|json_data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|---|----|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|1  |NA  |"
	{
	    \"location\": \"US\",
	    \"topLevelArray\": [
	        {
	            \"id\": 1,
	            \"secondLevelArray\": [
	                {
	                    \"key\": \"someKey\",
	                    \"operator\": \"=\",
	                    \"value\": 10
	                },
	                {
	                    \"key\": \"foo\",
	                    \"operator\": \">=\",
	                    \"value\": 5
	                },
	                {
	                    \"key\": \"someOtherKey\",
	                    \"operator\": \">\",
	                    \"value\": 647
	                }
	            ]
	        },
	        {
	            \"id\": 2,
	            \"secondLevelArray\": [
	                {
	                    \"key\": \"blah\",
	                    \"operator\": \"<\",
	                    \"value\": 7
	                }
	            ]
	        }
	    ]
	}
	"|
|2  |EU  |"
	{
	    \"location\": \"poland\",
	    \"topLevelArray\": [
	        {
	            \"id\": 2,
	            \"secondLevelArray\": [
	                {
	                    \"key\": \"bar\",
	                    \"operator\": \"<\",
	                    \"value\": 10
	                },
	                {
	                    \"key\": \"moo\",
	                    \"operator\": \">=\",
	                    \"value\": 16
	                },
	                {
	                    \"key\": \"baz\",
	                    \"operator\": \"!=\",
	                    \"value\": 9
	                }
	            ]
	        }
	    ]
	}
	"                                                                                                                                                                                                                                                                      |

As expected, we just get back everything.

Now, let’s start to drill down into the JSON object. First we will select the row id and just the data from the topLevelArray.

SELECT
	sj.id
	topLevelArray
FROM
	sample_json sj,
	json_array_elements(json_data -> 'topLevelArray') topLevelArray
;

|id |toplevelarray                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|---|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|1  |"{
	            \"id\": 1,
	            \"secondLevelArray\": [
	                {
	                    \"key\": \"someKey\",
	                    \"operator\": \"=\",
	                    \"value\": 10
	                },
	                {
	                    \"key\": \"foo\",
	                    \"operator\": \">=\",
	                    \"value\": 5
	                },
	                {
	                    \"key\": \"someOtherKey\",
	                    \"operator\": \">\",
	                    \"value\": 647
	                }
	            ]
	        }"|
|1  |"{
	            \"id\": 2,
	            \"secondLevelArray\": [
	                {
	                    \"key\": \"blah\",
	                    \"operator\": \"<\",
	                    \"value\": 7
	                }
	            ]
	        }"                                                                                                                                                                                                                                                                                                                                    |
|2  |"{
	            \"id\": 2,
	            \"secondLevelArray\": [
	                {
	                    \"key\": \"bar\",
	                    \"operator\": \"<\",
	                    \"value\": 10
	                },
	                {
	                    \"key\": \"moo\",
	                    \"operator\": \">=\",
	                    \"value\": 16
	                },
	                {
	                    \"key\": \"baz\",
	                    \"operator\": \"!=\",
	                    \"value\": 9
	                }
	            ]
	        }"             |

The “columns” selected are the id and topLevelArray. The id is straightforward. The topLevelArray is a lateral join clause statement and we use the json_array_elements() function to select the contents of the json_data.topLevelArray key.

We can continue to traverse deeper into the JSON object with another lateral join clause that accesses the next nested array from the topLevelArray key.

SELECT
	sj.id,
	secondLevelElements
FROM
	sample_json sj,
	json_array_elements(json_data -> 'topLevelArray') topLevelArray,
	json_array_elements(topLevelArray -> 'secondLevelArray') secondLevelElements
;

|id |secondlevelelements                                                                                                                                   |
|---|----------------------------------------------------------------------------------------------------------------------------------------------------|
|1  |"{
	                    \"key\": \"someKey\",
	                    \"operator\": \"=\",
	                    \"value\": 10
	                }"      |
|1  |"{
	                    \"key\": \"foo\",
	                    \"operator\": \">=\",
	                    \"value\": 5
	                }"          |
|1  |"{
	                    \"key\": \"someOtherKey\",
	                    \"operator\": \">\",
	                    \"value\": 647
	                }"|
|1  |"{
	                    \"key\": \"blah\",
	                    \"operator\": \"<\",
	                    \"value\": 7
	                }"          |
|2  |"{
	                    \"key\": \"bar\",
	                    \"operator\": \"<\",
	                    \"value\": 10
	                }"          |
|2  |"{
	                    \"key\": \"moo\",
	                    \"operator\": \">=\",
	                    \"value\": 16
	                }"         |
|2  |"{
	                    \"key\": \"baz\",
	                    \"operator\": \"!=\",
	                    \"value\": 9
	                }"          |

We add an additional FROM clause. This one referencing the result of the previous FROM clause.

json_array_elements(json_data -> 'topLevelArray') topLevelArray,
json_array_elements(topLevelArray -> 'secondLevelArray') secondLevelElements

Now we have access to the elements in the secondLevelArray and we can add a WHERE clause to select only what we want from that nested array.

select
	sj.id,
	secondLevelElements ->> 'key'
from
	sample_json sj,
	json_array_elements(json_data -> 'topLevelArray') topLevelArray,
	json_array_elements(topLevelArray -> 'secondLevelArray') secondLevelElements
where
	secondLevelElements ->> 'operator' = '>='
;

|id |?column?|
|---|--------|
|1  |foo     |
|2  |moo     |

The result being the row id, and the value of the key field in inner most nested object.

Query for Finding the nth Element in a PostgreSQL JSONB Array that Matches a Specified Value

Posted on by rchapin

The following is a sample query that you can use to search for rows that have the nth element in a JSONB array that match a specific value.

Given

CREATE TABLE sample_jsonb (
  id serial PRIMARY KEY,
  name varchar(64),
  json_data jsonb
);

INSERT INTO sample_jsonb(name, json_data)
VALUES
  ('foo',
  '{
  "key": "val1",
  "arr": ["homer", "bart", "barney"]
  }'),
  ('bar',
  '{
  "key": "val2",
  "arr": ["marge", "lisa", "maggie"]
  }'),
  ('baz',
  '{
  "key": "val2",
  "arr": ["bart", "milhouse", "nelson"]
  }')
;

We now have two records in the database where the string “bart” is contained in the arr array of values. The following query will select the row(s) where the “bart” string is in the 2nd (index 1) element of the array.

SELECT *
FROM sample_jsonb
WHERE
    (json_data->'arr'->1)::jsonb ? 'bart'

Query for Finding an Element in a PostgreSQL JSONB Array

Posted on by rchapin

The following is a sample query that you can use to search for rows that have an element in a JSONB array.

Given

CREATE TABLE sample_jsonb (
  id serial PRIMARY KEY,
  name varchar(64),
  json_data jsonb
);

INSERT INTO sample_jsonb(name, json_data)
VALUES
  ('foo',
  '{
  "key": "val1",
  "arr": ["homer", "bart", "barney"]
  }'),
  ('bar',
  '{
  "key": "val2",
  "arr": ["marge", "lisa", "maggie"]
  }')
;

select count(*) from sample_jsonb where json_data->'arr' ? 'marge';

PostgreSQL Query to Find Tables With Name LIKE

Posted on by rchapin

Sometimes you are working with a PostgreSQL database with A LOT of tables and looking for tables that contain a sub-string in their name. Following is a query that you can run that will return all of the tables that have the string in their name:

SELECT
  table_schema,
  table_name
FROM
  information_schema.tables
WHERE
      table_name LIKE '%<string>%'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY
  table_name, table_schema
;
© 2005 Ryan Chapin