JSON a XML v PostgreSQL

SELECT '{"b": 1,  "a": 2, "a": 3}'::json  AS json,
       '{"b": 1,  "a": 2, "a": 3}'::jsonb AS jsonb;

           json            |      jsonb
---------------------------+------------------
 {"b": 1,  "a": 2, "a": 3} | {"a": 3, "b": 1}

SELECT '{"cena": 199.90, "velke": 1e2}'::jsonb;
-- {"cena": 199.90, "velke": 100}

CREATE TABLE objednavka (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  data jsonb NOT NULL,
  CONSTRAINT data_je_objekt CHECK (jsonb_typeof(data) = 'object')
);

INSERT INTO objednavka (data) VALUES
 ('{"zakaznik":{"jmeno":"Jana","mesto":"Brno"},"stav":"nova","stitky":["expres","firma"],
    "polozky":[{"kod":"A1","ks":2,"cena":199.9},{"kod":"B7","ks":1,"cena":49}]}'),
 ('{"zakaznik":{"jmeno":"Petr","mesto":"Praha"},"stav":"odeslana","stitky":["firma"],
    "polozky":[{"kod":"A1","ks":1,"cena":199.9}]}'),
 ('{"zakaznik":{"jmeno":"Eva","mesto":"Brno"},"stav":"nova","polozky":[]}');

SELECT id,
       data->'zakaznik'          AS objekt,     -- jsonb
       data->'zakaznik'->>'jmeno' AS jmeno,      -- text
       data#>>'{polozky,0,kod}'  AS prvni_kod   -- text podle cesty
FROM objednavka ORDER BY id;

 id |               objekt                | jmeno | prvni_kod
----+-------------------------------------+-------+-----------
  1 | {"jmeno": "Jana", "mesto": "Brno"}  | Jana  | A1
  2 | {"jmeno": "Petr", "mesto": "Praha"} | Petr  | A1
  3 | {"jmeno": "Eva", "mesto": "Brno"}   | Eva   |

-- obsahuje tuto podstrukturu?                     → 1, 3
SELECT id FROM objednavka WHERE data @> '{"stav":"nova","zakaznik":{"mesto":"Brno"}}';

-- existuje klíč na nejvyšší úrovni?                  → 1, 2
SELECT id FROM objednavka WHERE data ? 'stitky';

-- obsahuje pole řetězec?                             → 1
SELECT id FROM objednavka WHERE data->'stitky' ? 'expres';

-- vrátí jsonpath aspoň jednu položku?                → 1, 2
SELECT id FROM objednavka WHERE data @? '$.polozky[*] ? (@.cena > 100)';

-- je predikát jsonpath pravdivý?                     → 3
SELECT id FROM objednavka WHERE data @@ '$.polozky.size() == 0';

SELECT o.id, p.kod, p.ks, p.cena
FROM objednavka o,
     JSON_TABLE(o.data, '$.polozky[*]' COLUMNS (
       kod  text    PATH '$.kod',
       ks   integer PATH '$.ks',
       cena numeric PATH '$.cena')) AS p
ORDER BY o.id, p.kod;

 id | kod | ks | cena
----+-----+----+-------
  1 | A1  |  2 | 199.9
  1 | B7  |  1 |    49
  2 | A1  |  1 | 199.9

SELECT data->'zakaznik'->>'mesto' AS mesto,
       jsonb_agg(jsonb_build_object('id', id, 'stav', data->>'stav') ORDER BY id) AS objednavky
FROM objednavka
GROUP BY 1 ORDER BY 1;

 mesto |                       objednavky
-------+--------------------------------------------------------
 Brno  | [{"id": 1, "stav": "nova"}, {"id": 3, "stav": "nova"}]
 Praha | [{"id": 2, "stav": "odeslana"}]

SELECT jsonb_agg(x) FROM (SELECT 1 AS a WHERE false) x;                  -- NULL
SELECT coalesce(jsonb_agg(x), '[]') FROM (SELECT 1 AS a WHERE false) x;   -- []

UPDATE objednavka SET data = jsonb_set(data, '{stav}', '"zaplacena"') WHERE id = 1;
UPDATE objednavka SET data = data || '{"poznamka":"volat předem"}'  WHERE id = 2;
UPDATE objednavka SET data = data - 'poznamka'                      WHERE id = 2;

-- univerzální: @>, ?, ?|, ?&, @?, @@
CREATE INDEX udalost_data_gin   ON udalost USING GIN (data);

-- menší, ale jen @>, @?, @@ (bez operátorů ?)
CREATE INDEX udalost_data_path  ON udalost USING GIN (data jsonb_path_ops);

-- jeden klíč porovnávaný na rovnost nebo rozsah: běžný B-strom nad výrazem
CREATE INDEX udalost_uzivatel   ON udalost ((data->>'uzivatel'));

EXPLAIN (COSTS OFF) SELECT count(*) FROM udalost WHERE data->>'uzivatel' = 'u42';
   ->  Bitmap Index Scan on udalost_uzivatel
         Index Cond: ((data ->> 'uzivatel'::text) = 'u42'::text)

EXPLAIN (COSTS OFF) SELECT count(*) FROM udalost WHERE data->'uzivatel' = '"u42"';
   ->  Parallel Seq Scan on udalost
         Filter: ((data -> 'uzivatel'::text) = '"u42"'::jsonb)

-- jen s indexem jsonb_path_ops:
EXPLAIN (COSTS OFF) SELECT count(*) FROM udalost WHERE data ? 'reklamace';
   ->  Seq Scan on udalost
         Filter: (data ? 'reklamace'::text)

SELECT '{"a":1}'::jsonb->>'b' IS NULL     AS chybi,          -- t
       '{"b":null}'::jsonb->>'b' IS NULL  AS null_hodnota,   -- t
       '{"b":null}'::jsonb ? 'b'          AS klic_existuje;  -- t

// Chybně: PDO považuje ? za placeholder
$st = $pdo->prepare("SELECT id FROM objednavka WHERE data ? 'stitky' AND data->>'stav' = ?");
// PDOException: syntax error at or near "$1"

// Správně: ?? se odešle jako operátor ?
$st = $pdo->prepare("SELECT id FROM objednavka WHERE data ?? 'stitky' AND data->>'stav' = ?");
$st->execute(['odeslana']);

// JSON parametr vždy přes json_encode, nikdy skládáním řetězce
$st = $pdo->prepare("SELECT id FROM objednavka WHERE data @> ?::jsonb");
$st->execute([json_encode(['zakaznik' => ['mesto' => 'Praha']], JSON_THROW_ON_ERROR)]);

SELECT xml '<a>1</a><b/>' IS DOCUMENT;        -- f: obsah (CONTENT), ne dokument
SELECT xml_is_well_formed_document('<a><b></a>');  -- f
SELECT '<a><b></a>'::xml;
-- ERROR:  invalid XML content
-- DETAIL:  line 1: Opening and ending tag mismatch: b line 1 and a

INSERT INTO feed VALUES (1, '<feed xmlns="http://www.w3.org/2005/Atom">
  <entry><title>První</title><updated>2026-10-01T08:00:00Z</updated></entry>
  <entry><title>Druhý</title><updated>2026-10-03T08:00:00Z</updated></entry>
</feed>');

SELECT xpath('//entry/title/text()', doc) FROM feed;            -- {}
SELECT xpath('//a:entry/a:title/text()', doc,
             ARRAY[ARRAY['a', 'http://www.w3.org/2005/Atom']])
FROM feed;                                                       -- {První,Druhý}

SELECT x.titulek, x.zmeneno
FROM feed,
     XMLTABLE(XMLNAMESPACES('http://www.w3.org/2005/Atom' AS a),
              '/a:feed/a:entry' PASSING doc
              COLUMNS titulek text        PATH 'a:title',
                      zmeneno timestamptz PATH 'a:updated') AS x;

 titulek |        zmeneno
---------+------------------------
 První   | 2026-10-01 11:00:00+03
 Druhý   | 2026-10-03 11:00:00+03

SELECT xmlelement(name objednavka, xmlattributes(42 AS id),
                  xmlelement(name zakaznik, 'Jana & syn'));
-- <objednavka id="42"><zakaznik>Jana &amp; syn</zakaznik></objednavka>