Setelah
panitia 2019-03 , pembekuan fitur terjadi. Kami memiliki kolom yang hampir tradisional di sini: kami
sudah menulis tentang pembekuan tahun lalu. Sekarang, hasil 2019: yang mana dari yang baru akan dimasukkan dalam PostgreSQL 12. Dalam bagian tinjauan JSONPath ini, contoh dan fragmen dari laporan "Postgres 12 in Etudes", yang dibaca Oleg Bartunov di
Saint Highload ++ di St. Petersburg pada 9 April tahun ini, digunakan.
Jsonpath
Semua yang terkait dengan JSON (B) relevan, diminati di dunia, di Rusia, dan ini adalah salah satu bidang pengembangan terpenting di Postgres Professional. Jenis jsonb, fungsi dan operator untuk bekerja dengan JSON / JSONB muncul dalam versi PostgreSQL 9.4, mereka dibuat oleh tim yang dipimpin oleh Oleg Bartunov.
Standar SQL / 2016 menyediakan untuk bekerja dengan JSON: JSONPath disebutkan di sana - satu set alat pengalamatan data di dalam JSON; JSONTABLE - cara mengubah JSON ke tabel biasa; Keluarga besar fungsi dan operator. Terlepas dari kenyataan bahwa JSON di Postgres telah didukung sejak lama, pada 2017 Oleg Bartunov dan rekan-rekannya mulai berupaya mendukung standar tersebut. Mematuhi standar selalu baik. Dari semua yang dijelaskan dalam standar, hanya satu tetapi tambalan yang paling penting adalah JSONPath di versi 12, jadi kita akan membicarakannya terlebih dahulu.
Di zaman kuno, orang menggunakan JSON, menyimpannya di bidang teks. Di 9.3, tipe data khusus untuk JSON muncul, tetapi fungsionalitas yang terkait dengannya tidak kaya, dan permintaan dengan tipe ini bekerja lambat karena waktu yang dihabiskan untuk mem-parsing representasi teks JSON. Ini menghentikan banyak pengguna Postgres potensial yang lebih suka database NoSQL. Produktivitas Postgres meningkat pada 9,4 ketika, berkat O. Bartunov, A. Korotkov dan F. Sigaev, Postgres memperkenalkan versi biner JSON - tipe jsonb.
jsonb tidak perlu diurai setiap saat, jadi bekerja dengannya jauh lebih cepat. Dari fungsi dan operator baru yang muncul pada saat yang sama, beberapa hanya bekerja dengan tipe biner yang baru, seperti operator penting dari kejadian
@> , yang memeriksa apakah elemen atau array termasuk dalam JSONB yang diberikan:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
memberikan TRUE, karena array di sisi kanan memasuki array di sebelah kiri. Tapi
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
akan memberikan FALSE, karena tingkat sarang berbeda, ia harus ditetapkan secara eksplisit. Apakah operator keberadaan diperkenalkan untuk tipe jsonb
? (tanda tanya) yang memeriksa apakah string adalah kunci objek atau elemen array di tingkat atas nilai JSONB, serta dua operator serupa lainnya (perincian di
sini ). Mereka didukung oleh indeks GIN dengan dua kelas operator GIN. Operator
-> (panah) memungkinkan Anda untuk "menavigasi" melalui JSONB, ia mengembalikan nilai dengan kunci atau, jika itu adalah array, dengan indeks. Ada
beberapa operator
lagi untuk bergerak. Tetapi tidak ada cara untuk mengatur filter yang bekerja serupa dengan DIMANA. Itu adalah terobosan: terima kasih kepada jsonb, Postgres mulai tumbuh dalam popularitas sebagai RDBMS dengan fitur NoSQL.
Pada tahun 2014, A. Korotkov, O. Bartunov dan F. Sigaev mengembangkan ekstensi jsquery, yang dimasukkan sebagai hasil dalam Postgres Pro Standard 9.5 (dan kemudian versi Standard and Enterprise). Ini menyediakan fitur tambahan yang sangat luas untuk bekerja dengan json (b). Ekstensi ini mendefinisikan bahasa permintaan untuk mengekstraksi data dari json (b) dan indeks untuk mempercepat permintaan ini. Fungsionalitas ini diperlukan oleh pengguna, mereka tidak siap untuk menunggu standar dan dimasukkannya fitur baru dalam versi vanilla. Nilai praktisnya juga dibuktikan dengan fakta bahwa pengembangan itu disponsori oleh Wargaming.net. Ekstensi mengimplementasikan tipe khusus - jsquery.
Kueri dalam bahasa ini ringkas dan terlihat, misalnya, seperti ini:
SELECT '{"apt":[{"no": 1, "rooms":2}, {"no": 2, "rooms":3}, {"no": 3, "rooms":2}]}'::jsonb @@ 'apt.#.rooms=3'::jsquery;
Kami bertanya di sini apakah ada "tiga rubel" di gedung apartemen. Tipe jsquery harus ditentukan karena operator @@ sekarang juga dalam tipe jsonb. Deskripsi ada di
sini , dan presentasi dengan banyak contoh ada di
sini .
Total: Postgres sudah memiliki segalanya untuk bekerja dengan JSON, dan kemudian muncul standar SQL: 2016. Ternyata semantiknya tidak begitu berbeda dengan semantik kita dalam ekstensi jsquery. Ada kemungkinan bahwa penulis standar bahkan melirik jsquery, menciptakan JSONPath. Tim kami harus menerapkan sedikit berbeda dari apa yang sudah kami miliki dan, tentu saja, banyak hal baru juga.
Lebih dari setahun yang lalu, pada commitfest bulan Maret, buah dari upaya pemrograman kami ditawarkan kepada komunitas dalam bentuk 3 tambalan besar dengan dukungan untuk standar
SQL: 2016 :
SQL / JSON: JSONPath;
SQL / JSON: fungsi;
SQL / JSON: JSON_TABLE.
Tetapi untuk mengembangkan tambalan bukan keseluruhan bisnis, mempromosikannya juga tidak mudah, terutama jika tambalan itu besar dan memengaruhi banyak modul. Banyak iterasi revisi revisi diperlukan, tambalan harus dipromosikan, seperti halnya perusahaan komersial, menginvestasikan banyak sumber daya (jam kerja). Kepala arsitek Postgres Professional, Alexander Korotkov, mengambilnya sendiri (karena dia sekarang memiliki status committer) dan mengamankan adopsi patch JSONPath - yang utama dalam rangkaian patch ini. Yang kedua dan ketiga sekarang dalam status Tinjauan Kebutuhan. JSONPath yang terfokus memungkinkan Anda untuk bekerja dengan struktur JSON (B) dan cukup fleksibel untuk menyoroti bagian-bagiannya. Dari 15 poin yang ditentukan dalam standar, 14 diimplementasikan, dan ini lebih dari pada Oracle, MySQL dan MS SQL.
Notasi JSONPath berbeda dari pernyataan Postgres untuk bekerja dengan notasi JSON dan JSQuery. Hirarki ditunjukkan oleh titik-titik:
$ .abc (dalam notasi postgres 11, saya harus menulis 'a' -> 'b' -> 'c');
$ - konteks elemen saat ini - pada kenyataannya, ekspresi dengan $ mendefinisikan wilayah json (b) yang akan diproses, termasuk yang ada di filter, sisanya tidak tersedia untuk pekerjaan;
@ - konteks saat ini dalam ekspresi filter - iterates atas jalur yang tersedia dalam ekspresi dengan $;
[*] - sebuah array;
* - wildcard, dalam ekspresi dengan $ atau @ berarti nilai segmen path apa pun, tetapi memperhitungkan hierarki;
** - sebagai bagian dari ekspresi dengan $ atau @ dapat berarti setiap nilai segmen path tanpa memperhitungkan hierarki - akan lebih mudah untuk menggunakannya jika Anda tidak mengetahui tingkat elemen yang bersarang;
operator "?" memungkinkan Anda mengatur filter yang mirip dengan WHERE:
$ .abc? (@ .x> 10);
$ .abcxtype (), juga size (), double (), ceiling (), floor (), abs (), datetime (), keyvalue () adalah metode.
Kueri dengan fungsi jsonb_path_query (tentang fungsi di bawah) mungkin terlihat seperti ini:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > 3)'); jsonb_path_query_array
Meskipun tambalan khusus dengan fungsi tidak dilakukan, tambalan JSONPath sudah memiliki fungsi utama untuk bekerja dengan JSON (B):
jsonb_path_exists('{"a": 1}', '$.a') true ( "?") jsonb_path_exists('{"a": 1}', '$.b') false jsonb_path_match('{"a": 1}', '$.a == 1') true ( "@>") jsonb_path_match('{"a": 1}', '$.a >= 2') false jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3, 4, 5 jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') 0 jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') [3, 4, 5] jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') [] jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)') 3 jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)') NULL
Perhatikan bahwa persamaan dalam ekspresi JSONPath adalah satu "=", sedangkan di jsquery itu ganda: "==".
Untuk ilustrasi yang lebih elegan, kami akan menghasilkan JSONB dalam satu pelat kolom rumah:
CREATE TABLE house(js jsonb); INSERT INTO house VALUES ('{ "address": { "city":"Moscow", "street": "Ulyanova, 7A" }, "lift": false, "floor": [ { "level": 1, "apt": [ {"no": 1, "area": 40, "rooms": 1}, {"no": 2, "area": 80, "rooms": 3}, {"no": 3, "area": 50, "rooms": 2} ] }, { "level": 2, "apt": [ {"no": 4, "area": 100, "rooms": 3}, {"no": 5, "area": 60, "rooms": 2} ] } ] }');
Gambar. 1 Perumahan pohon JSON dengan apartemen daun yang dialokasikan.Ini adalah JSON yang aneh: ia memiliki hierarki yang membingungkan, tetapi diambil dari kehidupan, dan dalam kehidupan sering diperlukan untuk bekerja dengan apa yang ada, dan bukan dengan apa yang seharusnya. Berbekal kemampuan versi baru, kami akan menemukan apartemen di lantai 1 dan 2, tetapi bukan yang pertama dalam daftar apartemen lantai (di pohon yang disorot dengan warna hijau):
SELECT jsonb_path_query_array(js, '$.floor[0, 1].apt[1 to last]') FROM house;
Di PostgreSQL 11, Anda harus menanyakan ini:
SELECT jsonb_agg(apt) FROM ( SELECT apt->generate_series(1, jsonb_array_length(apt) - 1) FROM ( SELECT js->'floor'->unnest(array[0, 1])->'apt' FROM house ) apts(apt) ) apts(apt);
Sekarang pertanyaan yang sangat sederhana: apakah ada baris yang berisi (di mana saja) nilai "Moskow"? Sangat sederhana:
SELECT jsonb_path_exists(js, '$.** ? (@ == "Moscow")') FROM house;
Di versi 11, Anda harus menulis skrip besar:
WITH RECURSIVE t(value) AS ( SELECT * FROM house UNION ALL ( SELECT COALESCE(kv.value, e.value) AS value FROM t LEFT JOIN LATERAL jsonb_each ( CASE WHEN jsonb_typeof(t.value) = 'object' THEN t.value ELSE NULL END ) kv ON true LEFT JOIN LATERAL jsonb_array_elements ( CASE WHEN jsonb_typeof(t.value) = 'array' THEN t.value ELSE NULL END ) e ON true WHERE kv.value IS NOT NULL OR e.value IS NOT NULL ) ) SELECT EXISTS (SELECT 1 FROM t WHERE value = '"Moscow"');
2 Pohon Perumahan JSON, Moskow ditemukan!Kami mencari apartemen apa pun di lantai apa pun dengan luas 40 hingga 90 meter persegi:
select jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area > 40 && @.area < 90)') FROM house; jsonb_path_query ----------------------------------- {"no": 2, "area": 80, "rooms": 3} {"no": 3, "area": 50, "rooms": 2} {"no": 5, "area": 60, "rooms": 2} (3 rows)
Kami mencari apartemen dengan kamar setelah tanggal 3, menggunakan jason perumahan kami:
SELECT jsonb_path_query(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query
Dan inilah cara jsonb_path_query_first bekerja:
SELECT jsonb_path_query_first(js, '$.floor.apt.no ? (@>3)') FROM house; jsonb_path_query_first
Kami melihat bahwa hanya nilai pertama yang dipilih yang memenuhi kondisi filter.
Operator Boolean JSONPath untuk JSONB @@ disebut operator yang cocok. Itu menghitung predikat JSONPath dengan memanggil fungsi jsonb_path_match_opr.
Operator boolean lain adalah @? - ini adalah tes keberadaan, menjawab pertanyaan apakah ekspresi JSONPath akan mengembalikan objek SQL / JSON, ia memanggil fungsi jsonb_path_exists_opr:
'[1,2,3]' @@ '$[*] == 3' true; '[1,2,3]' @? '$[*] @? (@ == 3)' - true
Hasil yang sama dapat dicapai dengan menggunakan berbagai operator:
js @? '$.a' js @@ 'exists($.a)' js @@ '$.a == 1' js @? '$ ? ($.a == 1)'
Keindahan operator JSONPath Boolean adalah bahwa mereka didukung, dipercepat oleh indeks GIN. jsonb_ops dan jsonb_path_ops adalah kelas operator yang sesuai. Pada contoh, kita menonaktifkan SEQSCAN, karena kita memiliki mikrotabel, pada tabel besar pengoptimal itu sendiri akan memilih Indeks Bitmap:
SET ENABLE_SEQSCAN TO OFF; CREATE INDEX ON house USING gin (js); EXPLAIN (COSTS OFF) SELECT * FROM house WHERE js @? '$.floor[*].apt[*] ? (@.rooms == 3)'; QUERY PLAN
Semua fungsi formulir jsonb_path_xxx () memiliki tanda tangan yang sama:
jsonb_path_xxx( js jsonb, jsp jsonpath, vars jsonb DEFAULT '{}', silent boolean DEFAULT false )
vars adalah objek JSONB untuk meneruskan variabel JSONPath:
SELECT jsonb_path_query_array('[1,2,3,4,5]', '$[*] ? (@ > $x)', vars => '{"x": 2}'); jsonb_path_query_array
Sulit dilakukan tanpa vars ketika kita membuat gabungan yang melibatkan bidang jenis jsonb di salah satu tabel. Katakanlah kita membuat aplikasi yang mencari apartemen yang cocok untuk karyawan di rumah itu yang telah menuliskan persyaratan mereka untuk area minimum dalam kuesioner:
CREATE TABLE demands(name text, position text, demand int); INSERT INTO demands VALUES ('','', 85), ('',' ', 45); SELECT jsonb_path_query(js, '$.floor[*].apt[*] ? (@.area >= $min)', vars => jsonb_build_object('min', demands.demand)) FROM house, demands WHERE name = ''; -[ RECORD 1 ]
Lucky Pasha dapat memilih dari 4 apartemen. Namun ada baiknya mengubah 1 huruf dalam permintaan - dari "P" ke "C", dan tidak akan ada pilihan! Hanya 1 apartemen yang akan melakukannya.
Satu lagi kata kunci tetap: silent adalah flag yang menekan penanganan kesalahan, mereka berada di hati nurani programmer.
SELECT jsonb_path_query('[]', 'strict $.a'); ERROR: SQL/JSON member not found DETAIL: jsonpath member accessor can only be applied to an object
Kesalahannya. Tapi ini tidak akan menjadi kesalahan:
SELECT jsonb_path_query('[]', 'strict $.a', silent => true); jsonb_path_query
Ngomong-ngomong, tentang kesalahan: sesuai dengan standar, kesalahan aritmatika dalam ekspresi tidak memberikan pesan kesalahan, mereka berada di hati nurani programmer:
SELECT jsonb_path_query('[1,0,2]', '$[*] ? (1/ @ >= 1)'); jsonb_path_query
Saat menghitung ekspresi dalam filter, nilai array dicari, di antaranya ada 0, tapi membaginya dengan 0 tidak menghasilkan kesalahan.
Fungsi akan bekerja secara berbeda tergantung pada mode yang dipilih: Strict atau Lax (dalam terjemahan "non-strict" atau bahkan "longgar", dipilih secara default). Misalkan kita mencari kunci dalam mode Lax di JSON, di mana jelas tidak:
SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)'; ?column?
Sekarang dalam mode Ketat:
SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)'; ?column?
Artinya, di mana dalam mode liberal kami menerima FALSE, dengan ketat kami mendapat NULL.
Dalam mode Lax, array dengan hierarki kompleks [1,2, [3,4,5]] selalu diperluas ke [1,2,3,4,5]:
SELECT jsonb '[1,2,[3,4,5]]' @? 'lax $[*] ? (@ == 5)'; ?column?
Dalam mode Ketat, angka "5" tidak akan ditemukan, karena tidak ada di bagian bawah hierarki. Untuk menemukannya, Anda harus memodifikasi kueri, mengganti "@" dengan "@ [*]":
SELECT jsonb '[1,2,[3,4,5]]' @? 'strict $[*] ? (@[*] == 5)'; ?column?
Dalam PostgreSQL 12, JSONPath adalah tipe data. Standar tidak mengatakan apa-apa tentang perlunya tipe baru, itu adalah properti implementasi. Dengan tipe baru, kami mendapatkan pekerjaan penuh dengan jsonpath dengan bantuan operator dan indeks mempercepat pekerjaan mereka, yang sudah ada untuk JSONB. Jika tidak, JSONPath harus diintegrasikan di tingkat kode pelaksana dan pengoptimal.
Anda dapat membaca tentang sintaks SQL / JSON, misalnya, di
sini .
Posting blog Oleg Bartunov adalah tentang
kesesuaian SQL / JSON standar-2016 untuk PostgreSQL, Oracle, SQL Server dan MySQL.
Berikut ini adalah
presentasi tentang SQL / JSON.
Dan di sini adalah
pengantar untuk SQL / JSON.