Dashboard date range with Field Filter

Get Help
1

Hi, I want to add a date range filter on the dashboard, the type I should use is Field Filter, but my SQL query with variables returns error:
No special value for parameter 3.

What does this mean? How to correct it? Thanks a lot.

In my case:
Database: SQL
The field content contains a json like this:

  {
    "date": "2023-09-14T07:39:21.842Z",
    "eventinfo": {
      "item": "111",
      "index": "0"
    },
    "sessionid": "MsSaLCsNJCeO2OtSxmWhWTcvzchp5iXC",
    "eventname": "results-click-card-item",
    "pageinfo": {
      "total": "198",
      "size": "20",
      "page": "1",
      "totalpages": "10",
      "params": "?query=test",
      "searchstring": "test"
    },
    "pagename": "pg-search"
  }

Part of SQL query:

select count(distinct(A.content->'pageInfo'->>'searchString')) as sub_count, DATE_TRUNC('month', TO_DATE(A.content->>'date', 'YYYY-MM')) as month
    from portal_logs A
    where A.content->'pageInfo'->'searchString' is not null
    [[and DATE_TRUNC('day', TO_DATE(A.content->>'date', 'YYYY-MM-DD')) between DATE_TRUNC('day', TO_DATE({{start_date}}, 'YYYY-MM-DD')) and DATE_TRUNC('day', TO_DATE({{end_date}}, 'YYYY-MM-DD'))]]
    group by A.content->>'sessionId', month

In my qustion start_date and end_date are Field Filter variables, associated to Content->Date, with filter type month and year.

2

those are not field filters. The field filter would be just

[[and {{filter]]
3

@Luiggi thank you for the reply.
I tried the following query, but I got the same error

select count(distinct(A.content->'pageInfo'->>'searchString')) as sub_count, DATE_TRUNC('month', TO_DATE(A.content->>'date', 'YYYY-MM')) as month
    from portal_logs A
    where A.content->'pageInfo'->'searchString' is not null
    [[and {{start_date}}]]
    group by A.content->>'sessionId', month

So my solution is using 2 Date variables:

    select count(distinct(A.content->'pageInfo'->>'searchString')) as sub_count, DATE_TRUNC('month', TO_DATE(A.content->>'date', 'YYYY-MM')) as month
    from portal_logs A
    where A.content->'pageInfo'->'searchString' is not null
    [[and TO_DATE(A.content->>'date', 'YYYY-MM') > {{start_date}}]]
    [[and TO_DATE(A.content->>'date', 'YYYY-MM') < {{end_date}}]]
    group by A.content->>'sessionId', month
4

Hello, before using date in field filter you need to be sure that you "column" was detected as a date type.
This link can help you : Working with JSON