sql

Querying based on JSON Fields

Assuming in the orders table, we have a metadata column that stores JSON data, we can use @> to query based on fields in that data.

SELECT metadata
FROM "orders"
WHERE metadata @> '{"brand": "sun"}';
metadata
--------
{
  "name": "apple",
  "price": "100",
  "brand": "sun",
}
-----------------------------
{
  "name": "banana",
  "price": "200",
  "brand": "sun",
}

Tags: sql

Concat String in PostgreSQL

Recently, I learnt that you can concatenate string in PostgreSQL using || instead of relying of CONCAT function. For example,

SELECT firstname || ' ' || lastname
FROM users;