zdb spotlite search query example

zdb-agg.sql

WITH results AS (
  select
    JSONB_ARRAY_ELEMENTS(
      zdb.extract_the_agg_data(
        index => 'spotlite_zdx'
      , field => 'organization_id'
      , response => arbitrary_agg::JSONB
      ) -> 'buckets'
    ) AS bucket
  FROM zdb.arbitrary_agg(
    index => 'spotlite_zdx'
  , query => dsl.bool(
      dsl.must(
        dsl.term('organization_id', 'o__62584d343b')
      , dsl.multi_match(
          fields => ARRAY['title', 'messages.content']
        , query => 'rand'
        , fuzziness => 2
        )
      )
    )
  , agg_json => JSON_BUILD_OBJECT(
      'the_agg', JSON_BUILD_OBJECT(
        'terms', JSON_BUILD_OBJECT(
          'field', 'related_type'
        )
      , 'aggs', JSON_BUILD_OBJECT(
          'results', JSON_BUILD_OBJECT(
            'top_hits', JSON_BUILD_OBJECT(
              'size', 5
             , '_source', ARRAY['related_type', 'related_id', 'title', 'organization_id']
            )
          )
        )
      )
    )
  )
)
SELECT
  bucket ->>'key' AS bucket
, CAST(bucket->>'doc_count' AS INT)  AS total
, bucket ->'results'->'hits'->'hits' AS hits
FROM results