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

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”

Query for Finding an Element in a PostgreSQL JSONB Array

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”

PostgreSQL Query to Find Tables With Name LIKE

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”