JSON example for PSQL

another_example.sql

-- JSON Table
WITH json_table AS (
    SELECT '{"action": "remove_newsletter_cta_session", "controller": "pages"}'::json AS json_field
)

-- Return values from JSON column
SELECT
    json_field->>'action' AS action,
    json_field->>'controller' AS controller
FROM json_table

json_example.sql

WITH json_cte AS (
    SELECT  '{"isbn":"123-456-222","author":{"lastname":"Doe","firstname":"Jane"},"editor":{"lastname":"Smith","firstname":"Markus"},"title":"The Ultimate Database Study Guide","category":["Non-Fiction","Technology"]}'::json as json_field
)

SELECT
    json_field->>'isbn' AS isbn,
    json_field->>'title' AS title,
    json_field->>'category' AS categories,
    json_field->'author'->>'firstname' AS firstname,
    json_field->'author'->>'lastname' AS lastname,
    CONCAT(json_field->'editor'->>'firstname', ' ', json_field->'editor'->>'firstname') AS editor_full_name
FROM json_cte