Category: SQL

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":
→ Continue reading “Query nested arrays in PostgreSQL JSON data”

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
;
→ Continue reading “PostgreSQL Query to Find Tables With Name LIKE”

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 → Continue reading “Unable to Set the Path To java.exe When Running Oracle SQL Developer Under Windows 7”

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})

  
→ Continue reading “Executing Dynamically Generated SQL Queries in a Shell Script and Saving the Output to a Variable”
© 2005 Ryan Chapin