syntax for Datastore: SQL Query

[tedmoorman]

Hello!

I'm looking at CMS OpenPayments data and trying to create a query using their API. I was referred to this page for support for the API's SQL syntax. For some reason, I'm having trouble specifying "not equals" in a WHERE clause. I've tried "<>", "!=", "WHERE NOT ...". Is there some place I can go to view the syntax for an SQL query of this API?

You can select from multiple datasets here: https://openpaymentsdata.cms.gov/datasets . For example, here is a specific dataset:
https://openpaymentsdata.cms.gov/dataset/0380bbeb-aea1-58b6-b708-829f92a48202 .

For example, I can create an "equals" request URL with the following syntax:

[SELECT * FROM 7dfb0fb2-e573-5448-a968-67899434ce8b][WHERE nature_of_payment_or_transfer_of_value = "Food and Beverage"][LIMIT 2]

I'm just not sure how to create the equivalent "not equals" request URL.

Thank you for taking a look at this!

[janette]

@tedmoorman using <> should work, but while we look into that you could try these other options:

GET

https://openpaymentsdata.cms.gov/api/1/datastore/query/7dfb0fb2-e573-5448-a968-67899434ce8b?keys=true&limit=25&offset=0&conditions[0][property]=nature_of_payment_or_transfer_of_value&conditions[0][value]=Food%20and%20Beverage&conditions[0][operator]=<>

POST

curl -X 'POST' \
  'https://openpaymentsdata.cms.gov/api/1/datastore/query/0380bbeb-aea1-58b6-b708-829f92a48202/0' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "conditions": [
    {
      "property": "nature_of_payment_or_transfer_of_value",
      "value": "Food and Beverage",
      "operator": "<>"
    }
  ],
  "limit": 2
}'

[tedmoorman]

@janette thank you for the response!

Is there a tool you're using to go from the parameters of interest to the URL string in the GET? Or, are you just concatenating all this on your own?

I would be interested in whatever process you're using for that, if you wouldn't mind posting!

[janette]

@tedmoorman you can write the query in json, then convert it with this https://www.convertonline.io/convert/json-to-query-string

or go to the filter screen, build your filters, and copy the query from the browser network tab

[tedmoorman]

@janette thank you for the suggestion!

I ended up building out the query in JSON and submitting it directly to a POST statement. R code is below:

library(tidyverse)
library(httr)
library(jsonlite)
library(fst)
library(readr)
library(readxl)

#Define conditions for extracting data as JSON text
json_text <- '{
  "conditions": [
    {
      "property": "nature_of_payment_or_transfer_of_value",
      "value": "Food and Beverage",
      "operator": "<>"  
    }
  ],
  "limit": 2
}'

#Define the base url to which each data set's ID is appended to
base_url <- 'https://openpaymentsdata.cms.gov/api/1/datastore/query/'

#Create a data frame of years and data set IDs
data_sets <- data.frame(
  matrix(
    c(
      2021, '0380bbeb-aea1-58b6-b708-829f92a48202'
    ),
    nrow = 1,
    ncol = 2,
    byrow = TRUE,
    dimnames = list(NULL,
                    c("year", "id"))
  ),
  stringsAsFactors = FALSE
)

#Create empty data frame to be appended to
api_results <- data.frame()

#Query and append all data sets
for(row in 1:nrow(data_sets)) {
  
  #Concatenate base URL with data set ID
  api_url <- paste0(base_url, data_sets[row, "id"])
  
  #Print out year being processed
  print(paste0("Processing year: ", data_sets[row, "year"]))
  
  #Query the API
  api_query <- POST(url = api_url,
                    body = json_text,
                    encode = "raw")
  
  #Append queried data to api_results data frame
  api_results <- bind_rows(api_results,
                           fromJSON(rawToChar(api_query$content))$results)
}

[tedmoorman]

@janette do you know how to format the JSON in an "OR" statement?

For example, suppose I wanted payments related to "Food and Beverage" or "Consulting". I couldn't use the following code because it works like an "AND" statement:

#Define conditions for extracting data as JSON text
json_text <- '{
  "conditions": [
    {
      "property": "nature_of_payment_or_transfer_of_value",
      "value": "Food and Beverage",
      "operator": "="  
    },
    {
      "property": "nature_of_payment_or_transfer_of_value",
      "value": "Consulting",
      "operator": "="  
    }
  ],
  "limit": 2
}'

If there's a good resource for documentation on formatting JSON queries, please let me know.

And thank you for all of your help!

[janette]

@tedmoorman Looks like Dan got you the answer here #3855

[tedmoorman]

Yes! Thank you for pointing that out, @janette .

And I appreciate all your help. I'm think I'm finally at the point where I can get what I need from the API.