Suche nach Werten in JSON Arrays

Verwenden Sie die Funktion, um festzustellen, ob ein bestimmter Wert in einem JSON -codierten Array existiert. json_array_contains

Die folgende Abfrage führt die Namen der Benutzer auf, die an "project2" teilnehmen.

WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'),
    (JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'),
    (JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}')
  ) AS t (users)
)
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset
WHERE json_array_contains(json_extract(users, '$.projects'), 'project2')

Diese Abfrage gibt eine Liste von Benutzern zurück.

+-------------+
| user        |
+-------------+
| Susan Smith |
+-------------+
| Jane Smith  |
+-------------+

Die folgende Beispielabfragespiel listet die Namen von Benutzern auf, die Projekte abgeschlossen haben, einschließlich der Anzahl abgeschlossener Projekte. Folgende Aktionen werden durchgeführt:

• Verwendet zum Zwecke der Übersicht SELECT-Anweisungen.

• Extrahiert das Projekt-Array.

• Wandelt das Array mittels CAST in einen nativen Array von Schlüssel/Wert-Paaren um.

• Extrahiert jedes einzelne Array-Element mit dem UNNEST-Operator.

• Filtert abgerufene Werte nach abgeschlossenen Projekten und zählt diese.

Anmerkung

Wenn MAP Sie CAST to verwenden, können Sie das Schlüsselelement als VARCHAR (native Zeichenfolge in Presto) angeben, den Wert jedoch unverändert lassenJSON, da es sich bei den Werten in um unterschiedliche Typen MAP handelt: Zeichenfolge für das erste Schlüssel-Wert-Paar und Boolean für das zweite.

WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith",
             "org": "legal",
             "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith",
             "org": "engineering",
             "projects": [{"name":"project2", "completed":true},
                          {"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith",
             "org": "finance",
             "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
),
employees AS (
  SELECT users, CAST(json_extract(users, '$.projects') AS
    ARRAY(MAP(VARCHAR, JSON))) AS projects_array
  FROM dataset
),
names AS (
  SELECT json_extract_scalar(users, '$.name') AS name, projects
  FROM employees, UNNEST (projects_array) AS t(projects)
)
SELECT name, count(projects) AS completed_projects FROM names
WHERE cast(element_at(projects, 'completed') AS BOOLEAN) = true
GROUP BY name

Diese Abfrage gibt das folgende Ergebnis zurück:

+----------------------------------+
| name        | completed_projects |
+----------------------------------+
| Susan Smith | 2                  |
+----------------------------------+
| Jane Smith  | 1                  |
+----------------------------------+