Jak uzyskać konkretny obiekt z tablicy jsonb w PostgreSQL?

Mam pole o nazwie „użytkownik”, które zawiera tablicę json, która z grubsza wygląda tak:

"user":

[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]

Teraz chcę zapytanie takie jak:

select count from tablename where id = "1"

Nie mogę uzyskać określonego pola countz tablicy obiektów json w PostgreSQL 9.4.

O wiele bardziej efektywne byłoby przechowywanie wartości w znormalizowanym schemacie. To powiedziawszy, możesz również sprawić, aby działał z bieżącą konfiguracją.

Założenia

Zakładając tę ​​definicję tabeli:

CREATE TABLE tbl (tbl_id int, usr jsonb);

„użytkownik” jest słowem zastrzeżonym i wymagałoby podwójnego cudzysłowu jako nazwy kolumny. Nie rób tego Używam usrzamiast tego.

Pytanie

Zapytanie nie jest tak trywialne, jak się wydawało (teraz usunięte):

SELECT t.tbl_id, obj.val->>'count' AS count
FROM   tbl t
JOIN   LATERAL jsonb_array_elements(t.usr) obj(val) ON obj.val->>'_id' = '1'
WHERE  t.usr @> '[{"_id":"1"}]';

Istnieją 3 podstawowe kroki :

1. Tanio identyfikuj kwalifikujące się rzędy

WHERE t.usr @> '[{"_id":"1"}]'identyfikuje wiersze z pasującym obiektem w tablicy JSON. Wyrażenie może używać ogólnego indeksu GIN w jsonbkolumnie lub indeksu z bardziej wyspecjalizowaną klasą operatora jsonb_path_ops:

CREATE INDEX tbl_usr_gin_idx ON tbl USING gin (usr jsonb_path_ops);

Dodana WHEREklauzula jest logicznie redundantna , ale wymagane jest użycie indeksu. Wyrażenie w klauzuli złączenia wymusza ten sam warunek, ale dopiero po odczekaniu tablicy w każdym kwalifikującym się wierszu. Dzięki obsłudze indeksu Postgres na początku przetwarza tylko wiersze zawierające kwalifikujący się obiekt. Nie ma większego znaczenia przy małych stołach, robi wielką różnicę przy dużych stołach i tylko kilku kwalifikujących się rzędach.

Związane z:

• Operator PostgreSQL używa indeksu, ale podstawowa funkcja nie
• Indeks znajdujący element w tablicy JSON

2. Zidentyfikuj pasujące obiekty w tablicy

Unnest with jsonb_array_elements(). ( unnest()jest dobry tylko dla typów tablic Postgres.) Ponieważ jesteśmy zainteresowani tylko dopasowaniem obiektów, od razu filtruj warunki łączenia.

Związane z:

• Jak zamienić tablicę Json w tablicę Postgres?

3. Wyodrębnij wartość dla zagnieżdżonego klucza 'count'

Po kwalifikujących obiekty zostały wydobyte, po prostu: obj.val->>'count'.