Postgres JSONB Cheatsheet.sql

-- Basic Selection
SELECT data FROM artifacts;                          -- Whole JSONB object
SELECT data->>'name' FROM artifacts;                 -- Get string value (->>')
SELECT data->'details' FROM artifacts;               -- Get JSON value (->)

-- Checking for existence/values
SELECT * FROM artifacts WHERE data ? 'name';         -- Has key 'name'?
SELECT * FROM artifacts WHERE data @> '{"type": "weapon"}';  -- Contains this JSON?
SELECT * FROM artifacts WHERE data->>'status' = 'active';    -- Exact value match

-- Nested objects
SELECT data->'details'->>'color' FROM artifacts;     -- Nested field as text
SELECT data #>> '{details,color}' FROM artifacts;    -- Same as above
SELECT data #> '{details}' FROM artifacts;          -- Nested field as JSON

-- Arrays
SELECT * FROM artifacts WHERE data->'tags' ? 'rare';  -- Array contains element
SELECT data->'scores'->0 FROM artifacts;             -- First array element
SELECT jsonb_array_elements(data->'tags') FROM artifacts;  -- Expand array

-- Updates
UPDATE artifacts 
SET data = jsonb_set(data, '{name}', '"new_name"');  -- Set field

UPDATE artifacts 
SET data = data || '{"status": "inactive"}'::jsonb;   -- Merge/add fields

UPDATE artifacts 
SET data = data - 'temporary_field';                  -- Remove field

UPDATE artifacts 
SET data = jsonb_set(
    data,
    '{details,color}',
    '"blue"',
    true                                              -- Create if not exists
);

-- Complex queries
SELECT * FROM artifacts 
WHERE data @> '{"details": {"level": 5}}'            -- Nested matching
AND data->'scores'->>0 = '10';                       -- Array element check

-- Aggregation
SELECT 
    jsonb_agg(data) as all_data,
    jsonb_object_agg(data->>'name', data->'score') as name_scores
FROM artifacts;

-- Type casting
SELECT data->>'count'::integer FROM artifacts;        -- Cast to integer