Author: rchapin

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”

Chili Garlic Tofu with Broccoli

Posted on by rchapin

Ingredients

  • 2 14 oz packages of extra firm tofu
  • 2 TB olive oil
  • Salt & pepper, to taste
  • 4 garlic cloves, minced
  • 1 lb. broccoli florets
  • 2 TB Sambal Olek or chili paste
  • 4 TB honey
  • 1 TB low sodium soy sauce
  • 4 sheets roasted seaweed
  • 1 TB sesame seeds
  • 1.5 cups basmati rice

Directions

  1. Blot tofu with paper towels while pressing down slightly to release water. Slice into 1/2 inch thick pieces and blot again with paper towels.
  2. Sauce:
→ Continue reading “Chili Garlic Tofu with Broccoli”

Vegetarian Beef Tips and Asparagus Stir Fry

Posted on by rchapin

Ingredients

  • 2 bags Gardein Be’f Tips1
  • 1 1/4 cup soy sauce
  • 4 tablespoons vegetable oil
  • 3 cloves garlic, minced
  • 1 tablespoon ginger, minced
  • 1 teaspoon brown sugar
  • 1/2 red onion, chopped
  • 1 red bell pepper, chopped
  • approx 1 1/2 lbs of asparagus cut into 2 inch pieces
  • 2 tablespoons balsamic vinegar
  • 1 cup chicken broth
  • 2 teaspoons cornstarch
  • 4 cups cooked rice

Directions

  1. In a small bowl, combine soy sauce, and brown sugar and set aside
  2. Heat a skillet
→ Continue reading “Vegetarian Beef Tips and Asparagus Stir Fry”

How to set up a locally hosted git server and create new repositories

Posted on by rchapin

You may have code and configurations that are required to stay on premise. As a result, you will need to setup your own git server and create and manage repositories locally. Following is an overview of how to set that up.

We will leave aside server setup, configuration, and networking and assume that we have a machine on which we will host the repos, git-server, and machines that will clone, pull, and push updates, clients.

Setting up the

→ Continue reading “How to set up a locally hosted git server and create new repositories”

Setting up software RAID on Debian with mdadm

Posted on by rchapin

Software RAID has come a long way. Unless you have some very high-rate, high-volume, I/O workloads with SLAs that will otherwise cost you money, for the most part a software RAID will perform just fine. One of the primary benefits of using software RAID is the portability of your disks/data. If a box on which you have a software RAID dies somehow and at least some (depending on your RAID configuration) of drives survive, you can easily resurrect the RAID → Continue reading “Setting up software RAID on Debian with mdadm”

Enable and disable delivery to additional email addresses for sent and received messages with a Google Workspace account

Posted on by rchapin

I have a Google Workspace account for my domain and sometimes it is useful to have messages sent from or sent too other addresses in my domain also delivered to my address.

To set it up, login to your Google Admin console, then in the left-hand navigation click on Google Workspace, and then Gmail.

Look for Routing and click on it to enter the routing configurations.

You can then add, modify, delete, enable, or disable routing rules for → Continue reading “Enable and disable delivery to additional email addresses for sent and received messages with a Google Workspace account”

[SOLVED] debsig-verify for Failed verification error, “signatures using the SHA1 algorithm are rejected” and “Can’t check signature: Invalid digest algorithm”

Posted on by rchapin

If you are using debsig-verify for the verification of a downloaded .deb file and are unable to verify it, run it with the -d option to get more information. If you see the following two lines

gpg: Note: signatures using the SHA1 algorithm are rejected
gpg: Can't check signature: Invalid digest algorithm

It is likely that the PGP signature used to sign the package uses the SHA1 algorithm which has been deprecated in most of the recent Linux distros. If → Continue reading “[SOLVED] debsig-verify for Failed verification error, “signatures using the SHA1 algorithm are rejected” and “Can’t check signature: Invalid digest algorithm””

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 → Continue reading “Query for Finding the nth Element in a PostgreSQL JSONB Array that Matches a Specified Value”

LVM Resize – reduce the size of one logical volume to enable expanding another

Posted on by rchapin

I’m running an Ubuntu workstation and when setting it up simply went the “next-next-next” route when setting up the encrypted disk via LVM. The default is to create a 1G swap partition which is just not enough when you attempt to run too many things and locks up and/or crashes the machine.

My goal was to reduce my /root partition and then use that space to extend my swap partition.

Ensure that you back up your data first! There is → Continue reading “LVM Resize – reduce the size of one logical volume to enable expanding another”

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';
→ Continue reading “Query for Finding an Element in a PostgreSQL JSONB Array”
© 2005 Ryan Chapin