Suchen nach Werten in JSON-Arrays

Um zu ermitteln, ob ein bestimmter Wert in einem JSON-kodierten Array vorhanden ist, nutzen Sie die json_array_contains-Funktion.

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 Sie CAST zum MAP verwenden, können Sie das Schlüsselelement als VARCHAR (native Zeichenfolge in Presto) angeben, behalten aber den Wert als JSON bei, da die Werte in MAP unterschiedliche Typen aufweisen: Das erste Schlüssel/Wert-Paar ist ein Zeichenfolgenwert und das zweite ein boolescher.

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                  |
+----------------------------------+