SQL JSONPATH Postgres 12

pg.sql

### Create Table
CREATE TABLE orders (
	id serial NOT NULL PRIMARY KEY,
	info jsonb NOT NULL
);

## Insert data
INSERT INTO orders (info)
VALUES('{ "customer": "Lily Bush", "items":34,"description":"foo"}'),
      ('{ "customer": "Josh William", "items":3,"description":"zoo"}'),
      ('{ "customer": "Mary Clark", "items":4,"description":"woo"}');
      
## Exact match

SELECT * FROM orders WHERE jsonb_path_match(info, '$.customer == "Lily Bush"')

## Regex match

SELECT * FROM orders WHERE jsonb_path_match(info, '$.customer like_regex "Lily"')

## Case insensitive regex

SELECT * FROM orders WHERE jsonb_path_match(lower(info::text)::jsonb, '$.customer like_regex "lily" flag "i"')

## Comparison operator

SELECT * FROM orders WHERE jsonb_path_match(info, '$.items > 3')

### Logical Operator

SELECT * FROM orders WHERE jsonb_path_match(info, '$.items > 1 && $.customer like_regex "Josh"')