Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

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

  • 26 October 2016
  • 7 replies
  • 1681 views
J

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”

}

7 replies

J

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.

P

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

P

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

aleks
Rank
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!

looker-ulti

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"]}

m4c4r

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.

ubya308

To save anyone else out there days of trial error and Googling here is the code to get greater / less than / not equal to

 

{
“model”:“thelook”,
“view”:“inventory_items”,
“fields”:[“category.name”,“inventory_items.days_in_inventory_tier”,“products.count”],
“filters”:{“category.name”:[“socks”,“shirts”], “category.id”:”<>2”},
“sorts”:[“products.count desc 0”],
“limit”:“500”,
“query_timezone”:“America/Los_Angeles”
}

Reply


Terms of Service