Suchen Sie nach Schlüsselwörtern in Arrays mit regexp_like

Die folgenden Beispiele veranschaulichen, wie Sie mit der Funktion regexp_like ein Dataset nach einem Schlüsselwort innerhalb eines Elements in einem Array durchsuchen. Sie übernimmt als Eingabe ein auszuwertendes reguläres Ausdrucksmuster oder eine Liste von Begriffen, die durch einen senkrechten Strich (|) voneinander getrennt sind, und bestimmt, ob diese Eingabe in der angegebenen Zeichenfolge enthalten ist.

Das reguläre Ausdrucksmuster muss innerhalb der Zeichenfolge enthalten sein, braucht aber nicht genau mit ihr übereinzustimmen. Wenn es mit der gesamten Zeichenfolge übereinstimmen soll, setzen Sie das Muster zwischen ein ^ am Anfang und ein $ am Ende, z. B. '^pattern$'.

Stellen Sie sich ein Array von Websites mit deren Hostname und einem flaggedActivity-Element vor. Dieses Element enthält ein ARRAY mit mehreren MAP-Elementen, die jeweils beliebte Schlüsselwörter und deren Beliebtheitswert enthalten. Angenommen, Sie möchten ein bestimmtes Schlüsselwort innerhalb einer MAP in diesem Array finden.

Um diesen Datensatz nach Websites mit einem bestimmten Schlüsselwort zu durchsuchen, verwenden wir regexp_like anstelle des ähnlichen SQL LIKE Operators, da die Suche nach einer großen Anzahl von Schlüsselwörtern mit regexp_like effizienter ist.

Beispiel 1: Verwenden von regexp_like

Die Abfrage in diesem Beispiel sucht mit der Funktion regexp_like nach den Begriffen 'politics|bigdata' in den Werten innerhalb des Arrays:

WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(ARRAY[
          MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
          MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
          MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
          MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   ),
   CAST(
     ROW('news.cnn.com', ROW(ARRAY[
       MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
       MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
       MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
       MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
     ])
     ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   ),
   CAST(
     ROW('netflix.com', ROW(ARRAY[
       MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
       MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
       MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
       MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
     ])
     ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   )
 ] AS items
),
sites AS (
  SELECT sites.hostname, sites.flaggedactivity
  FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)

Diese Abfrage gibt zwei Websites zurück:

+----------------+
| hostname       |
+----------------+
| aws.amazon.com |
+----------------+
| news.cnn.com   |
+----------------+

Beispiel 2: Verwenden von regexp_like

Bei der Abfrage im folgenden Beispiel werden die Gesamtbeliebtheitswerte für die Websites, die mit Ihren Suchbegriffen übereinstimmen, mit der Funktion regexp_like addiert und dann von hoch nach niedrig sortiert.

WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(ARRAY[
          MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
          MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
          MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
          MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    ),
    CAST(
      ROW('news.cnn.com', ROW(ARRAY[
        MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
        MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
        MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
        MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    ),
    CAST(
      ROW('netflix.com', ROW(ARRAY[
        MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
        MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
        MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
        MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    )
  ] AS items
),
sites AS (
  SELECT sites.hostname, sites.flaggedactivity
  FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname, array_agg(flags['term']) AS terms, SUM(CAST(flags['count'] AS INTEGER)) AS total
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)
ORDER BY total DESC

Diese Abfrage gibt zwei Websites zurück:

+------------------------------------+
| hostname       | terms    | total  |
+----------------+-------------------+
| news.cnn.com   | politics |  241   |
+----------------+-------------------+
| aws.amazon.com | bigdata |  10    |
+----------------+-------------------+