Bagaimanakah Saya Boleh Mencari Elemen Dengan Cekap Dalam Tatasusunan JSON dalam PostgreSQL?

Petua untuk mencari elemen tatasusunan JSON yang cekap dalam PostgreSQL

Untuk mengoptimumkan carian untuk elemen tertentu dalam tatasusunan JSON, pastikan anda mempertimbangkan versi PostgreSQL dan struktur data.

PostgreSQL 9.4 dan JSONB

Dalam PostgreSQL 9.4 dan lebih baru, jenis data JSON binari jsonb menyediakan keupayaan pengindeksan yang dipertingkatkan dengan ketara. Anda boleh mencipta indeks GIN terus pada tatasusunan jsonb:

<code class="language-sql">CREATE TABLE tracks (id serial, artists jsonb);  -- !
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);</code>

Indeks ini membenarkan pertanyaan yang cekap menggunakan operator @>:

<code class="language-sql">SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';</code>

Sebagai alternatif, anda boleh menggunakan jsonb_path_ops kelas pengendali GIN khusus:

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);  -- !</code>

Pernyataan pertanyaan kekal tidak berubah.

PostgreSQL 9.3 dan JSON

Untuk PostgreSQL 9.3, pertimbangkan untuk menggunakan fungsi IMMUTABLE untuk mengekstrak elemen tatasusunan JSON ke dalam tatasusunan nilai teks:

<code class="language-sql">CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';</code>

Kemudian, buat indeks GIN berfungsi pada tatasusunan yang diekstrak:

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));</code>

Dalam pertanyaan, gunakan operator tatasusunan untuk memadankan ungkapan indeks:

<code class="language-sql">SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));</code>

Sila ambil perhatian bahawa pengindeksan fungsi hanya boleh digunakan dengan fungsi IMMUTABLE. Sahkan kebolehubahan fungsi json_array_elements() dan sebarang fungsi JSON lain yang digunakan.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Mencari Elemen Dengan Cekap Dalam Tatasusunan JSON dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!