Postgres: Manipulate JSON rows
Explaining the syntax
Keywords according to this image:
When selecting a JSON key from an object:
* Using "->"
* Returns a JSON object
* Result is still of type json or jsonb
* Using "->>"
* Returns a text value
* Result is a text (string) — the actual value inside the JSON
* You can't chain further JSON operations on it
When updating a JSON key from an object:
* Using jsonb_set(table, '{object}', 'key-value', 'boolean_create_if_not_exists)
* Example: jsonb_set(bill, '{state'}', '1', true)
* Will create the key if it doesn't exist
* Using jsonb_set(table, '{object}', 'value')
* Example: jsonb_set(bill, '{state'}', '1')
* Will not create the key if it does not exist
Querying an object inside of an object
This example uses tms_booking. Example on how to query a specific field inside of multiple JSON nestings:
SELECT
jsonb_array_elements(bill->'trip'->'route')->>'uuid' AS route_uuid
FROM trip_bill;