Category: Databases

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
;

Solution for MySQL ERROR 1396 (HY000): Operation CREATE USER failed for

Posted on by rchapin

This indicates that the user already exists, or did exist but that all of the data for that user has not been deleted.

As the mysql root user:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'hostname';
DROP USER 'user'@'hostname'

Then re-try creating the user.

Backspace, Delete, and/or Return Key Stops Working in Oracle SQL Developer

Posted on by rchapin

So, I fire up SQL Developer to run some queries against a QC server and for some reason, I am no longer able to use the backspace, delete, or return keys to edit .sql files opened in the program.

I tried opening a new .sql file, and restarting SQL Developer.  I then tried restarting Windows.  None of those worked.

After a bit of searching I found a forum posting that indicated by going to Tools/Preferences/Accelerators and clicking the “Load Preset…” button in the bottom right of the dialog box would fix the problem.  My guess is that some key mapping preference file had gotten corrupted some how and that by replacing it with a default that it fixes the problem.

After doing so, I was back in business.

Unable to Set the Path To java.exe When Running Oracle SQL Developer Under Windows 7

Posted on by rchapin

I was trying to run Oracle SQL Developer for the first time on a new machine.  When firing it up, it presented me with a dialog box asking me to “Enter the full pathname for the java.exe file”.

OK, no problem.  So I find the path to the java.exe binary that was just installed with the SDK.  Hit submit . . . and nothing happens.  It blanks out the text field and the dialog box stares back at me.

I tried pointing it to the java.exe that was in the jre dir.  No joy.

After a lot of futzing around and doing some searches it turns out that there are a few things that you have to do to get it to run for the first time.

First, right-click on the Oracle SQL Developer short-cut and select “Run as Administrator”.

Then, in the dialog box, click Browse and navigate to the JDK that comes with the Oracle install.  For me it was in C:\Oracle11G\11.2.0.3\jdk\bin\java.exe

Once I did that it fired right up.  I then quit, and ran it as my user and it seemed to start up just fine.

Executing Dynamically Generated SQL Queries in a Shell Script and Saving the Output to a Variable

Posted on by rchapin

If you would like to, in a shell script, dynamically generate SQL queries for MySQL and save the output of those queries to a variable that you can then use in the script, here is an example:

#!/bin/bash

for i in `cat tables_list.txt`
do

   # Build the query
   QUERY="SELECT count(*) FROM ${i}"

   # Run the query from the command-line and save the
   # output into the $ROW_COUNT variable
   ROW_COUNT=$(echo $QUERY | mysql -u${USER_NAME} -p${PASSWORD} -h ${HOST} -P ${PORT} --skip-column-names ${DBASE})

   # Do something with the var...
   echo -n -e "$ROW_COUNT\t" >> $OUT_FILE
   echo "$i" >> $OUT_FILE

done;

Figuring out MySQL ERROR 1005 (HY000) Can’t create table (errno: 150)

Posted on by rchapin

So I’m dumping a database on a remote server to pull down to my local box to do some development.  When loading the mysqldump file I encountered the error:

ERROR 1005 (HY000) at line 8680: Can't create table 'database.table' (errno: 150)

After doing some searching online it seems that is one of the notoriously cryptic MySQL error messages that is woefully overloaded.

I did manage to fix the error (which ended up being a foreign key reference from another table to the one that was erroring out whereby the column definitions were not exactly the same) and learned two important things:

  1. You can invoke the following mysql command immediately after the error to get a more verbose error message (which is what enabled me to solve the problem): SHOW INNODB STATUS
  2. Jason Hinkle has a very nice online reference with a number of possible culprits, and their solutions are listed.

Executing MySQL Queries and Commands from the Command Line to a Remote Server

Posted on by rchapin

Following are a couple of ways to execute SQL on a remote MySQL server via the shell.

Executing SQL directly from the command line:

$ mysql -u uid -p -h remote.host database -e ‘SQL query here;’

Executing SQL from a file on the local host on the remote server:

$ mysql -u uid -p -h remote.host database < file.sql

You can also connect to the remote mysql server and from the command line execute SQL from files on the local drive as such (as long as the file is in the same directory from which the mysql connection is made

mysql> \. file.sql

Notice NO ‘;’ at the end of the command.

Moreover, you can use the following to run commands in your local shell

mysql> \! ls

Will give you the directly listing of the dir from which you invoked the mysql remote connection.

mysql > \! vi file.sql

Will allow you to edit the file on your local drive without having to close the mysql cli.

© 2005 Ryan Chapin