Return to site

Postgresql json select

broken image

WHERE employee_data - > 'department' = 'Marketing'

broken image

Find employees in the 'Marketing' department SELECT * FROM employees Check if the employee has a skill in 'Sales' SELECT * FROM employees Extract the employee's name SELECT employee_data - > 'name' AS employee_name Suppose you have a table called employees with a jsonb column named employee_data. Some commonly used functions and operators include:

broken image

You can query jsonb columns using various JSON functions and operators provided by PostgreSQL. It provides several advantages over the standard json type, especially when it comes to querying and indexing JSON data. It is an extension of the json data type. In PostgreSQL, jsonb is a data type used to store JSON (JavaScript Object Notation) data in a more efficient and optimized binary format. This query first type casts the value of preferences.beta from JSON to boolean and then filters the rows where it's true.

broken image

SELECT preferences - > 'beta' FROM users WHERE (preferences - > 'beta' ):: boolean IS TRUE

broken image