Czy można przechowywać i przesyłać zapytania do JSON w SQLite?

Muszę przechowywać obiekty JSON w bazie danych SQLite, a następnie wykonywać na nim złożone zapytania.

Zrobiłem taki stół:

+--------------------------------------+
|document |  property | string | number|
+--------------------------------------+
|foo      |  "title"  | "test" |       | 
+--------------------------------------+
|foo      |  "id"     |        |  42   | 
+--------------------------------------+
|bar      |  "id"     |        |  43   | 
+--------------------------------------+

dla dwóch obiektów

foo {"title": "test", "id": 42} 
bar {id: 43}

Ale nie mogę wykonywać zapytań „ORAZ”, takich jak:

SELECT DISTINCT id  FROM table WHERE title = "test" AND id = 42

jak widzisz, część po „GDZIE” to totalny nonsens, ale nie mam pojęcia, jak utworzyć zapytanie, które zrobiłoby to, co chcę.

Czy uważasz, że istnieje lepszy sposób na przechowywanie moich danych lub obejście tego problemu?

I oczywiście JSON może zawierać dowolną właściwość, więc nie mogę utworzyć tabeli z kolumnami dla każdej właściwości.

Używam WebSQL, który jest SQLite bez rozszerzeń.

Wiem, że moje pytanie jest dość konkretne, ale czy mógłbyś mi pomóc?

sqlite json

— tuxlu
źródło

Jeśli jest to naprawdę ważne dla Twojej aplikacji, prawdopodobnie lepiej jest użyć albo prawdziwego magazynu dokumentów („NoSQL”), albo np. Przejść na Postgres, który łączy w sobie bardzo wydajną pamięć JSON z silnymi funkcjami relacyjnymi.

— a_horse_w_no_name

Przeczytaj dokumentację .

— CL.

@CL. Tak. To także pierwszy wynik, kiedy googling dla „sqlite json” :)

— Izzy

Musisz mieć konta i połączyć je: dba.stackexchange.com/users/81459/tuxlu i dba.stackexchange.com/users/81513/tuxlu Możesz skorzystać z tej strony pomocy: dba.stackexchange.com/help/merging-accounts

— Julien Vavasseur

@Izyzy Po pierwsze, dla mnie przynajmniej to pytanie jest teraz pierwszym rezultatem, a nie dokumentami. Po drugie, to rozszerzenie nie jest dobrą odpowiedzią dla OP, który wyraźnie stwierdził, używając „WebSQL, czyli SQLite bez rozszerzeń”.

— OJFord

Odpowiedzi:

SQLite 3.9 wprowadził nowe rozszerzenie ( JSON1 ), które pozwala łatwo pracować z danymi JSON.

Wprowadzono także obsługę indeksów wyrażeń , co (w moim rozumieniu) powinno umożliwić również zdefiniowanie indeksów danych JSON.

— Fizyk
źródło

PostgreSQL ma kilka fajnych funkcji do przechowywania JSON. Możesz wybrać wartości JSON wykonując

SELECT DISTINCT FROM TABLE WHERE foo->title=test AND id=42

Możesz także indeksować określone klucze w obiekcie JSON, jeśli używasz tego jsonbtypu.

— redneckProgrammer
źródło

Istniejące odpowiedzi na ten temat dotyczą faktycznego przechowywania danych PO jako JSON (co może być lepszym rozwiązaniem jego podstawowego problemu).

Jednak rzeczywistym pytaniem było, jak znaleźć dokumenty w podanej tabeli w stylu EAV, na podstawie wielu właściwości. Być może przydatna byłaby odpowiedź na to pytanie.

Standardowym sposobem wykonywania tego, co mówisz, jest INTERSECTION.

(SELECT document FROM table WHERE property="id" AND number=43)
INTERSECTION 
(SELECT document FROM table WHERE property="title" AND string="test")

Możesz to również zrobić za pomocą samodzielnego łączenia, jeśli wolisz.

SELECT t1.document
FROM table as t1, table as t2
WHERE t1.document = t2.document
AND t1.property="id" AND t1.number=43
AND t2.property="title" AND t2.string="test"

Oczywiście bardziej „poprawnym” i prawdopodobnie najbardziej wydajnym sposobem byłoby po prostu utworzenie kolumny dla każdej potrzebnej właściwości, na przykład „id” i „title”.

— Thomas Ahle
źródło