JSONB-Operatoren

CREATE TABLE products (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku   text NOT NULL,
    attrs jsonb
);

INSERT INTO products (sku, attrs) VALUES
    ('A1', '{
        "color": "red",
        "weight_kg": 1.2,
        "tags": ["new", "sale"],
        "dimensions": {"width": 10, "height": 20, "depth": 5},
        "in_stock": true
    }'),
    ('B2', '{
        "color": "blue",
        "weight_kg": 0.5,
        "tags": ["bestseller"],
        "dimensions": {"width": 5, "height": 8, "depth": 3},
        "in_stock": false
    }');

myapp=> SELECT
          attrs->'color'   AS color_jsonb,
          attrs->>'color'  AS color_text
        FROM products WHERE sku = 'A1';
 color_jsonb | color_text
-------------+------------
 "red"       | red

-- Achtung: color_jsonb hat Anführungszeichen (es ist ein JSON-String).
-- color_text ist ein "richtiger" String ohne Anführungszeichen.

myapp=> SELECT
          attrs->'tags'      AS all_tags,
          attrs->'tags'->>0  AS first_tag,
          attrs->'tags'->>1  AS second_tag
        FROM products WHERE sku = 'A1';
      all_tags     | first_tag | second_tag
-------------------+-----------+------------
 ["new", "sale"]  | new       | sale

myapp=> SELECT
          attrs#>'{dimensions,width}'   AS w_jsonb,
          attrs#>>'{dimensions,width}'  AS w_text
        FROM products WHERE sku = 'A1';
 w_jsonb | w_text
---------+--------
 10      | 10

-- Äquivalent zur längeren Form:
myapp=> SELECT attrs->'dimensions'->>'width' FROM products WHERE sku='A1';

-- Produkte mit color = 'red'
SELECT sku FROM products
WHERE attrs @> '{"color": "red"}';

-- Produkte mit Tag "sale"
SELECT sku FROM products
WHERE attrs @> '{"tags": ["sale"]}';

-- Mehrere Bedingungen kombiniert
SELECT sku FROM products
WHERE attrs @> '{"color": "red", "in_stock": true}';

-- Produkte mit ALLEN Tags 'new' und 'sale':
SELECT sku FROM products
WHERE attrs @> '{"tags": ["new", "sale"]}';
-- Liefert nur A1 — B2 hat zwar 'bestseller', aber nicht 'new' und 'sale'.

myapp=> SELECT
          sku,
          attrs ? 'color'     AS has_color,
          attrs ? 'discount'  AS has_discount
        FROM products;
 sku | has_color | has_discount
-----+-----------+--------------
 A1  | t         | f
 B2  | t         | f

-- Produkte mit Tag 'sale' (Array-Mitgliedschaft)
SELECT sku FROM products WHERE attrs->'tags' ? 'sale';
-- A1

-- Schlüssel: hat 'discount' ODER 'promo' ODER 'sale_price'
SELECT sku FROM products
WHERE attrs ?| array['discount', 'promo', 'sale_price'];

-- Hat 'color' UND 'weight_kg' UND 'in_stock'
SELECT sku FROM products
WHERE attrs ?& array['color', 'weight_kg', 'in_stock'];

myapp=> SELECT '{"a": 1, "b": 2}'::jsonb || '{"b": 99, "c": 3}'::jsonb;
        ?column?
----------------------------
 {"a": 1, "b": 99, "c": 3}

-- Discount-Feld hinzufügen, ohne andere zu verlieren
UPDATE products
SET attrs = attrs || '{"discount": 15}'::jsonb
WHERE sku = 'A1';

-- Mehrere Felder auf einmal
UPDATE products
SET attrs = attrs || '{"discount": 15, "promo_until": "2026-06-01"}'::jsonb
WHERE sku = 'A1';

SELECT '{"dim": {"width": 10, "height": 20}}'::jsonb
    || '{"dim": {"depth": 5}}'::jsonb;
--                ?column?
-- ---------------------------------
--  {"dim": {"depth": 5}}
-- "width" und "height" sind weg!

-- Schlüssel entfernen
SELECT '{"a": 1, "b": 2}'::jsonb - 'b';
--     ?column?
-- ---------------
--  {"a": 1}

-- Mehrere Schlüssel entfernen
SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['b', 'c'];
--     ?column?
-- ---------------
--  {"a": 1}

-- Aus Array das Element an Index n entfernen
SELECT '["a", "b", "c"]'::jsonb - 1;
--   ?column?
-- ------------
--  ["a", "c"]

-- discount-Feld entfernen
UPDATE products
SET attrs = attrs - 'discount'
WHERE sku = 'A1';

-- Tieferes Feld entfernen
UPDATE products
SET attrs = attrs #- '{dimensions, depth}'
WHERE sku = 'A1';