Hoe download ik alle documenten in een periode met raadsinformatie?

Vragen
,
1

Zie:

Hoe kan ik de achterliggende _msearch uitlezen via Invantive UniversalSQL en dan massaal alle documenten in een periode oppakken ter voeding van Claude AI?

De payload voor POST naar _msearch op https://api.openraadsinformatie.nl/v1/elastic/*/_msearch is:

{"preference":"ResultList01"}
{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "must": [
              {
                "bool": {
                  "must": [
                    {
                      "simple_query_string": {
                        "fields": [
                          "text",
                          "title",
                          "description",
                          "name"
                        ],
                        "default_operator": "OR",
                        "query": "\"Ede\""
                      }
                    },
                    {
                      "terms": {
                        "_index": [
                          "ori_*",
                          "osi_*",
                          "owi_*"
                        ]
                      }
                    },
                    {
                      "term": {
                        "@type": "MediaObject"
                      }
                    }
                  ],
                  "must_not": [
                    {
                      "match": {
                        "@type": "Membership"
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "should": [
                    {
                      "terms": {
                        "_index": [
                          "ori_ede_20250412110204"
                        ]
                      }
                    }
                  ]
                }
              },
              {
                "range": {
                  "last_discussed_at": {
                    "gte": "2025-01-01T00:00:00",
                    "lte": "2027-03-01T00:00:00",
                    "boost": 2
                  }
                }
              }
            ]
          }
        }
      ]
    }
  },
  "highlight": {
    "pre_tags": [
      "<mark>"
    ],
    "post_tags": [
      "</mark>"
    ],
    "fields": {
      "text": {},
      "title": {},
      "name": {},
      "description": {}
    },
    "fragment_size": 100,
    "number_of_fragments": 3
  },
  "size": 20,
  "_source": {
    "includes": [
      "*"
    ],
    "excludes": []
  },
  "aggs": {
    "_index": {
      "terms": {
        "field": "_index",
        "size": 500,
        "order": {
          "_count": "desc"
        }
      }
    }
  },
  "from": 0,
  "sort": [
    {
      "last_discussed_at": {
        "order": "desc"
      }
    }
  ]
}

met een antwoord zoals:

{
  "took": 13,
  "responses": [
    {
      "took": 13,
      "timed_out": false,
      "_shards": {
        "total": 331,
        "successful": 331,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": {
          "value": 10000,
          "relation": "gte"
        },
        "max_score": null,
        "hits": []
      },
      "aggregations": {
        "tags.http://www.w3.org/1999/02/22-rdf-syntax-ns#_8.https://argu.co/ns/meeting/tag.keyword": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": []
        }
      },
      "status": 200
    }
  ]
}
2

Een voorbeeld is:

create or replace table subscriptions@InMemoryStorage
as
select t.id
,      t.recipient_name
,      t.recipient_email
,      t.index_filter
,      t.text_filter
,      t.rel_day_start
,      t.rel_day_end
,      replace
       ( case
         when t.index_filter like 'ori%'
         then 'Gemeente {0}'
         when t.index_filter like 'osi%'
         then 'Provincie {0}'
         when t.index_filter like 'owi%'
         then 'Waterschap {0}'
         when t.index_filter like 'ggm%'
         then 'Tweede Kamer'
         else 'Alle'
         end
       , '{0}'
       , initcap(substr(t.index_filter, 5))
       )
       index_label
,      to_char(trunc(sysdateutc) - t.rel_day_start, 'dd-mm-yyyy') rel_date_start_label
,      to_char(trunc(sysdateutc) - t.rel_day_end, 'dd-mm-yyyy') rel_date_end_label
,      to_char(trunc(sysdateutc) - t.rel_day_start, 'yyyy-mm-dd"t"hh24:mi:ss"z"') rel_date_start_c
,      to_char(trunc(sysdateutc) - t.rel_day_end, 'yyyy-mm-dd"t"hh24:mi:ss"z"') rel_date_end_c
from   ( select 1 id
         ,      'John Doe' recipient_name
         ,      'john.doe@acme.eu' recipient_email
         ,      'ori_harderwijk' index_filter
         ,      null text_filter
         ,      14 rel_day_start
         ,      0 rel_day_end
         union all
         select 2 id
         ,      'John Doe' recipient_name
         ,      'john.doe@acme.eu' recipient_email
         ,      'ori_amersfoort' index_filter
         ,      null text_filter
         ,      28 rel_day_start
         ,      0 rel_day_end
         union all
         select 3 id
         ,      'John Doe' recipient_name
         ,      'john.doe@acme.eu' recipient_email
         ,      'ori_ede' index_filter
         ,      null text_filter
         ,      28 rel_day_start
         ,      0 rel_day_end
       ) t

create or replace table actions@inmemorystorage
as
select ssn.id
,      ssn.recipient_name
,      ssn.recipient_email
,      ssn.rel_date_start_label
,      ssn.rel_date_end_label
,      ssn.index_filter
,      ssn.index_label
,      'https://api.openraadsinformatie.nl/v1/elastic/' || coalesce(ssn.index_filter, '*') || '/_search' url
,      'POST' method
,      'application/json' acceptMimeType
,      'application/json' contentType
,      true diskCache
,      3600 diskCacheMaxAgeSec
,      ''
       || '{ "query":'
       || '  { "bool":'
       || '    { "must":'
       || '      [ { "bool":'
       || '          { "must":'
       || '            [ { "bool":'
       || '                { "must":'
       || '                  [ { "term": { "@type": "MediaObject" } }'
       || case
          when ssn.text_filter is not null
          then '                  , { "simple_query_string":'
               || '                   { "fields": [ "text", "title","description", "name" ]'
               || '                   , "default_operator": "OR"'
               || '                   , ' || jsonelement('query', ssn.text_filter)
               || '                   }'
               || '                 }'
          end
       || '                  ]'
       || '                , "must_not":'
       || '                  [ { "match": { "@type": "Membership" } }'
       || '                  ]'
       || '                }'
       || '              }'
       || case
          when ssn.rel_day_start is not null or ssn.rel_day_end is not null
          then '            , { "range":'
               || '             { "last_discussed_at":'
               || '               { ' || jsonelement('gte', ssn.rel_date_start_c)
               || '               , ' || jsonelement('lte', ssn.rel_date_end_c)
               || '               }'
               || '             }'
               || '           }'
          end
       || '            ]'
       || '          }'
       || '        }'
       || '      ]'
       || '    }'
       || '  }'
       || ', "size": 250'
       || ', "_source":'
       || '  { "includes": [ "*" ]'
       || '  , "excludes": []'
       || '  }'
       || ', "from": 0'
       || ', "sort":'
       || '  [ { "last_discussed_at": { "order": "desc" } }'
       || '  ]'
       || '}'
       textPayload
,      'zori' || to_char(ssn.id) cacheKey
,      true ignoreWebError
,      30000 timeoutMs
from   subscriptions@InMemoryStorage ssn

insert into smtp@mail
( toEmail
, ccEmail
, bccEmail
, subject
, body
, attachment1Contents
, attachment1Name
, attachment1MimeType
)
select atn.recipient_email
       toEmail
,      cast(null as varchar2)
       ccEmail
,      cast(null as varchar2)
       bccEmail
,      'Open Raadsinformatie van ' 
       || atn.index_label
       || ' (' 
       || atn.rel_date_start_label
       || ' - ' 
       || atn.rel_date_end_label
       || ')'
       subject       
,      'Zie bijlage voor raadsstukken van '
       || atn.index_label
       || ' voor de periode ' 
       || atn.rel_date_start_label
       || ' - ' 
       || atn.rel_date_end_label
       || '.'
       body
,      ascii_to_blob(htp.CONTENTS_CHAR)
       attachment1Contents
,      atn.index_label
       || ' van '
       || atn.rel_date_start_label
       || ' tot '
       || atn.rel_date_end_label
       || '.json' 
       attachment1Name
,      'text/plain'
       attachment1MimeType
from   ACTIONS@InMemoryStorage atn
join   HTTPDOWNLOAD@DataDictionary
       ( atn.url
       , atn.acceptMimeType
       , timeoutMs => atn.timeoutMs
       , contentType => atn.contentType
       , method => atn.method
       , diskCache => atn.diskCache
       , diskCacheMaxAgeSec => atn.diskCacheMaxAgeSec
       , textPayload => atn.textPayload
       , cacheKey => atn.cacheKey
       , ignoreWebError => atn.ignoreWebError
       ) htp

In de stuurtabel subscriptions kunnen filters op gemeente en periode vastgelegd worden.

Het verwerken duurt circa 1 seconde per subscription.

3

Deze vraag is automatisch gesloten na 1 week inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.

4

Dit topic is 3 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.