Question

Looker API: Run Inline Query: Filters: How to pass OR condition and date filters

  • 26 October 2016
  • 6 replies
  • 1477 views

Hi,


I am trying to use Looker API: run_inline_query(result_format, body).

It works as expected with simple filter conditions based on equality. I am also able to get it work with multiple equality based conditions.



  1. I am struggling to provide, multiple values for 1 condition with an OR. So, the example below filters on (socks AND shirts). I want to filter on (socks OR shirts) - Just like looker interface.

  2. How do I provide non equality bases conditions: For e.g. date between ‘2001-01-01’ and ‘2001-12-31’?

  3. How do I check IS NULL?

  4. How do I check for functions like substring (contains)?

  5. How do I provide negative conditions - like ‘NOT equal to’ or ‘does not contain’?


I am very well familiar with SQL but new to JSON.


Body format is:

{

“model”:“thelook”,

“view”:“inventory_items”,

“fields”:[“category.name”,“inventory_items.days_in_inventory_tier”,“products.count”],

“filters”:{“category.name”:[“socks”,“shirts”]},

“sorts”:[“products.count desc 0”],

“limit”:“500”,

“query_timezone”:“America/Los_Angeles”

}


6 replies

Nevermind… I figured it out. 🙂

I was able to create the body for an existing look using its slug.


I can now add any filter to the look, create the body and see how its implemented in the body.

Hi can you please share what was the solution for this. I am also struggling for the same issue.

@jaknap Can you please share the solution you found for passing the OR condition to inline query body?

Userlevel 2

Hi @pmetha


The best way to find out what values need to be inserted is to run the desired query in the UI explore, then go to Admin --> Queries panel and see the query id that was run. Then run the query(query_id) API call to see in what format was the query generated.


If in the explore I use the following custom filter: ${users.city} = "London" OR ${users.last_name} = "Smith", the generated API format will be "filter_expression": "${users.city} = \"London\" OR ${users.last_name} = \"Smith\""


I hope this clarifies it!

I am trying a date expression and can only get a single date to work in the filter…

I need the following to work----

https://lookerdev-looker.mia.ucloud.int:19999/api/3.0/queries/run/json

{"model":"performance-reporting","view":"acf_request_filtered","fields":["feedback.start_date","feedback.topic_name","feedback.all_responses_received","person.full_name","owner_person.full_name","topic.anonymous"],"filters":{"feedback.start_date":["2020-11-26" , range of dates - OR AN EXPRESSION ]},"sorts":["feedback.start_date desc 0"]}

Hi @looker-ulti,

 

I’ve discovered that if you click Get LookML on an Explore you can see the yaml version of the query:

...
filters:
billing.usage_start_date: 2021/02/21 to 2021/03/22
...

If you then translate the filter seen to JSON, it appears to work fine with the API.

Reply