Zapytanie JSONB w PostgreSQL

Mam tabelę, personsktóra zawiera dwie kolumny idoraz datakolumnę opartą na JSONB (ta tabela została właśnie stworzona w celach demonstracyjnych do zabawy z obsługą JSON w PostgreSQL).

Teraz przypuszcza, że ​​zawiera dwa rekordy:

1, { name: 'John', age: 30 }
2, { name: 'Jane', age: 20 }

Teraz przypuszczam, że chcę uzyskać imię każdej osoby w wieku powyżej 25 lat. Próbowałem:

select data->'name' as name from persons where data->'age' > 25

Niestety powoduje to błąd. Mogę rozwiązać to za pomocą ->>zamiast ->, ale wtedy porównania nie działają już zgodnie z oczekiwaniami, ponieważ nie porównuje się liczb, ale ich reprezentacje jako ciągi znaków:

select data->'name' as name from persons where data->>'age' > '25'

Potem zorientowałem się, że mogę rozwiązać problem, używając ->i obsady int:

select data->'name' as name from persons where cast(data->'age' as int) > 25

To działa, ale nie jest miło, że muszę znać rzeczywisty typ (typ agedokumentu JSON jest numberzresztą, więc dlaczego PostgreSQL nie może tego sam zrozumieć?).

Potem zorientowałem się, że jeśli ręcznie przekonwertuję na textużycie ::składni, wszystko też działa zgodnie z oczekiwaniami - chociaż teraz ponownie porównujemy łańcuchy.

select data->'name' as name from persons where data->'age'::text > '25'

Jeśli następnie wypróbuję to z nazwą zamiast wieku, to nie działa:

select data->'name' as name from persons where data->'name'::text > 'Jenny'

Powoduje to błąd:

niepoprawna składnia wejściowa dla typu json

Najwyraźniej nic tu nie rozumiem. Niestety, ciężko jest znaleźć rzeczywiste przykłady użycia JSON z PostgreSQL.

Jakieś wskazówki?

To nie działa, ponieważ próbuje rzucić jsonbwartość na integer.

select data->'name' as name from persons where cast(data->'age' as int) > 25

To faktycznie działałoby:

SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;

Lub krócej:

SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;

I to:

SELECT data->'name' AS name FROM persons WHERE data->>'name' > 'Jenny';

Wygląda na to zamieszanie z dwoma operatorami ->i->> oraz pierwszeństwa operatora . Rzutowanie ::wiąże się silniej niż operatory json (b).

Dobierz typ dynamicznie

To jest bardziej interesująca część twojego pytania:

typ wieku w dokumencie JSON jest w każdym razie liczbą, więc dlaczego PostgreSQL nie może tego sam zrozumieć?

SQL jest językiem ściśle wpisanym, nie pozwala na ocenę tego samego wyrażenia integerw jednym wierszu i textw następnym. Ale ponieważ interesuje Cię tylko booleanwynik testu, możesz obejść to ograniczenie z CASEwyrażeniem, które rozwidla się w zależności od wyniku jsonb_typeof():

SELECT data->'name'
FROM   persons
WHERE  CASE jsonb_typeof(data->'age')
        WHEN 'number'  THEN (data->>'age')::numeric > '25' -- treated as numeric
        WHEN 'string'  THEN data->>'age' > 'age_level_3'   -- treated as text
        WHEN 'boolean' THEN (data->>'age')::bool           -- use boolean directly (example)
        ELSE FALSE                                         -- remaining: array, object, null
       END;

Niepisany ciąg literału po prawej stronie >operatora jest automatycznie wymuszany na odpowiednim typie wartości po lewej stronie. Jeśli umieścisz tam wpisaną wartość, typ musi być zgodny lub musisz rzucić ją jawnie - chyba że w systemie zarejestrowano odpowiednią ukrytą rzutowanie.

Jeśli wiesz, że wszystkie wartości liczbowe są w rzeczywistości integer, możesz również:

... (data->>'age')::int > 25 ...