Querying data

SlicingDice provides several ways so you can analyse or extract your data defining queries. It is possible to query your databases through our API (using JSON or the SQL endpoint) and also through the SQL drivers. You can learn more about the SQL drivers or about our API capabilities in the respective documentation.

Below you'll learn how to execute specific queries for different purposes using JSON or SQL.

Counting entities

Count entities queries let you know how many unique entities you have in a particular dimension that satisfies the defined query conditions.

In order to make a count entities query, you need to send a POST request to https://api.slicingdice.com/v1/query/count/entity with an array of JSON objects (or a single JSON object) of queries identified by unique names (query-name ), each containing a list of columns and conditions and operators, if any.

The query below will count how many unique entities there are on the dimension users, that have the name equals to Jeff and clicks equals to Pay Now between 2019-03-25T00:00:00Z and 2019-04-08T00:00:00Z .

Dimension value

If you do not provide a value to dimension, the query will be performed over the default dimension.


SELECT
  COUNT([entity-id])
FROM
  users
WHERE
  name = 'Jeff'
  AND [clicks.value] = 'Pay Now'
  and [clicks.date] BETWEEN '2019-03-25T00:00:00Z'
  AND '2019-04-08T00:00:00Z';


$ curl -X POST https://api.slicingdice.com/v1/sql \
    -H 'Authorization: MASTER_OR_WRITE_API_KEY' \
    -H 'Content-Type: application/sql' \
    -d 'SELECT COUNT([entity-id]) FROM users WHERE name = 'Jeff' AND [clicks.value] = 'Pay Now' and [clicks.date] BETWEEN '2019-03-25T00:00:00Z' AND '2019-04-08T00:00:00Z';'


curl -X POST https://api.slicingdice.com/v1/query/count/entity \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '[
        {
            "query-name": "jeff-user-clicked-to-pay",
            "dimension": "users",
            "query": [
                {
                    "name": {
                        "equals": "Jeff"
                    }
                },
                "and",
                {
                    "clicks": {
                        "equals": "Pay Now",
                        "between": [
                            "2019-03-25T00:00:00Z",
                            "2019-04-08T00:00:00Z"
                        ]
                    }
                }
            ],
            "bypass-cache": true
        }
    ]'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = [
    {
        "dimension": "users",
        "query-name": "jeff-user-clicked-to-pay",
        "bypass-cache": True,
        "query": [
            {
                "name": {
                    "equals": "Jeff"
                }
            },
            "and",
            {
                "clicks": {
                    "equals": "Pay Now",
                    "between": [
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    ]
                }
            }
        ]
    }
]

print(slicingdice.count_entity(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONArray queryData = new JSONArray()
        .put(new JSONObject()
            .put("dimension", "users")
            .put("query-name", "jeff-user-clicked-to-pay")
            .put("bypass-cache", true)
            .put("query", new JSONArray()
                .put(new JSONObject()
                    .put("name", new JSONObject()
                        .put("equals", "Jeff")))
                .put("and")
                .put(new JSONObject()
                    .put("clicks", new JSONObject()
                        .put("equals", "Pay Now")
                        .put("between", new JSONArray()
                            .put("2019-03-25T00:00:00Z")
                            .put("2019-04-08T00:00:00Z"))))));
        JSONObject result = slicingdice.countEntity(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = [
    {
        "dimension" => "users",
        "query-name" => "jeff-user-clicked-to-pay",
        "bypass-cache" => true,
        "query" => [
            {
                "name" => {
                    "equals" => "Jeff"
                }
            },
            "and",
            {
                "clicks" => {
                    "equals" => "Pay Now",
                    "between" => [
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    ]
                }
            }
        ]
    }
]

puts slicingdice.count_entity(query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = [
    {
        "dimension": "users",
        "query-name": "jeff-user-clicked-to-pay",
        "bypass-cache": true,
        "query": [
            {
                "name": {
                    "equals": "Jeff"
                }
            },
            "and",
            {
                "clicks": {
                    "equals": "Pay Now",
                    "between": [
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    ]
                }
            }
        ]
    }
];

slicingdice.countEntity(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    array(
        "dimension" => "users",
        "query-name" => "jeff-user-clicked-to-pay",
        "bypass-cache" => true,
        "query" => array(
            array(
                "name" => array(
                    "equals" => "Jeff"
                )
            ),
            "and",
            array(
                "clicks" => array(
                    "equals" => "Pay Now",
                    "between" => array(
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    )
                )
            )
        )
    )
);

print_r($slicingdice->countEntity($queryData));
?	>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new List{
                new Dictionary{
                    {"dimension", "users"},
                    {"query-name", "jeff-user-clicked-to-pay"},
                    {"bypass-cache", true},
                    {"query", new List{
                        new Dictionary{
                            {"name", new Dictionary{
                                {"equals", "Jeff"}
                            }}
                        },
                        "and",
                        new Dictionary{
                            {"clicks", new Dictionary{
                                {"equals", "Pay Now"},
                                {"between", new List{
                                    "2019-03-25T00:00:00Z",
                                    "2019-04-08T00:00:00Z"
                                }}
                            }}
                        }
                    }}
                }
            };

            var result = slicingdice.CountEntity(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := []interface{}{
        map[string]interface{}{
            "dimension": "users",
            "query-name": "jeff-user-clicked-to-pay",
            "bypass-cache": true,
            "query": []interface{}{
                map[string]interface{}{
                    "name": map[string]interface{}{
                        "equals": "Jeff",
                    },
                },
                "and",
                map[string]interface{}{
                    "clicks": map[string]interface{}{
                        "equals": "Pay Now",
                        "between": []interface{}{
                            "2019-03-25T00:00:00Z",
                            "2019-04-08T00:00:00Z",
                        },
                    },
                },
            },
        },
    }

    fmt.Println(slicingdice.CountEntity(queryData))
}
}

Counting events

Count event queries let you know the number of times some data was inserted for event columns in a particular dimension.

In order to make a count events query, you need to send a POST request to https://api.slicingdice.com/v1/query/count/event with an array of JSON objects (or a single JSON object) of queries identified by unique names (query-name ), each containing a list of conditions.

The query below will count how many times the event visited-page for the actions column happened between 2019-03-25T00:00:00Z and 2019-04-08T00:00:00Z .


SELECT
  COUNT(*)
FROM
  users
WHERE
  [actions.value] = 'visited-page'
  AND [actions.date] BETWEEN '2019-03-25T00:00:00Z'
  AND '2019-04-08T00:00:00Z';


$ curl -X POST https://api.slicingdice.com/v1/sql \
    -H 'Authorization: MASTER_OR_WRITE_API_KEY' \
    -H 'Content-Type: application/sql' \
    -d 'SELECT COUNT(*) FROM users WHERE [actions.value] = 'visited-page' AND [actions.date] BETWEEN '2019-03-25T00:00:00Z' AND '2019-04-08T00:00:00Z';'


curl -X POST https://api.slicingdice.com/v1/query/count/event \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '[
    {
        "query-name": "visited-page-events",
        "query": [
            {
                "actions": {
                    "equals": "visited-page",
                    "between": [
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    ]
                }
            }
        ]
    }
]'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = [
    {
        "query": [
            {
                "actions": {
                    "equals": "visited-page",
                    "between": [
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    ]
                }
            }
        ],
        "query-name": "visited-page-events"
    }
]

print(slicingdice.count_event(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONArray queryData = new JSONArray()
        .put(new JSONObject()
            .put("query", new JSONArray()
                .put(new JSONObject()
                    .put("actions", new JSONObject()
                        .put("equals", "visited-page")
                        .put("between", new JSONArray()
                            .put("2019-03-25T00:00:00Z")
                            .put("2019-04-08T00:00:00Z")))))
            .put("query-name", "visited-page-events"));
        JSONObject result = slicingdice.countEvent(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = [
    {
        "query" => [
            {
                "actions" => {
                    "equals" => "visited-page",
                    "between" => [
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    ]
                }
            }
        ],
        "query-name" => "visited-page-events"
    }
]

puts slicingdice.count_event(query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = [
    {
        "query": [
            {
                "actions": {
                    "equals": "visited-page",
                    "between": [
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    ]
                }
            }
        ],
        "query-name": "visited-page-events"
    }
];

slicingdice.countEvent(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    array(
        "query" => array(
            array(
                "actions" => array(
                    "equals" => "visited-page",
                    "between" => array(
                        "2019-03-25T00:00:00Z",
                        "2019-04-08T00:00:00Z"
                    )
                )
            )
        ),
        "query-name" => "visited-page-events"
    )
);

print_r($slicingdice->countEvent($queryData));>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new List{
                new Dictionary{
                    {"query", new List{
                        new Dictionary{
                            {"actions", new Dictionary{
                                {"equals", "visited-page"},
                                {"between", new List{
                                    "2019-03-25T00:00:00Z",
                                    "2019-04-08T00:00:00Z"
                                }}
                            }}
                        }
                    }},
                    {"query-name", "visited-page-events"}
                }
            };

            var result = slicingdice.CountEvent(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := []interface{}{
        map[string]interface{}{
            "query": []interface{}{
                map[string]interface{}{
                    "actions": map[string]interface{}{
                        "equals": "visited-page",
                        "between": []interface{}{
                            "2019-03-25T00:00:00Z",
                            "2019-04-08T00:00:00Z",
                        },
                    },
                },
            },
            "query-name": "visited-page-events",
        },
    }

    fmt.Println(slicingdice.CountEvent(queryData))
}

Aggregations

Aggregations are really powerful for generating insights from the data by calculating metrics, such as maximum, minimum, sum and average, as well as crossing data from different columns to generate pivot tables. If you are used to Relational Databases, imagine the power of crossing data from different tables without the performance hit of multiple JOIN operations.

In order to make an aggregation query, you need to send a POST request to https://api.slicingdice.com/v1/query/aggregation with a single JSON object containing a list of columns and predicates.

The query below will count how many unique entities there are on the dimension users , grouped by the top 2 most common values for the purchased-products column between 2019-03-25T00:00:00Z and 2019-04-08T00:00:00Z, considering only entities where country column is equals to USA .

Multi-level Aggregations are not commutative

Keep in mind the results are generated by recursively applying aggregations, i.e., when making an aggregation between the age and city columns, it first find the top N values for age and, based on these values, it will find the top N values for city. In this sense, multi-level aggregation is not commutative.


SELECT
  [purchased-products.value]
FROM
  users
WHERE
  country = 'USA'
GROUP BY
  [purchased-products.value]
HAVING
  [purchased-products.date] BETWEEN '2019-03-23T00:00:00Z'
  AND '2019-04-11T00:00:00Z'
LIMIT
  2;


  $ curl -X POST https://api.slicingdice.com/v1/sql \
    -H 'Authorization: MASTER_OR_WRITE_API_KEY' \
    -H 'Content-Type: application/sql' \
    -d 'SELECT [purchased-products.value] FROM users WHERE country='USA'
        GROUP BY [purchased-products.value] HAVING [purchased-products.date]
        BETWEEN '2019-03-20T00:00:00Z' AND '2019-04-08T00:00:00Z' LIMIT 2;'


curl -X POST https://api.slicingdice.com/v1/query/aggregation \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
        "filter": [
            {
                "country": { "equals": "USA" }
            }
        ],
        "query": [
            {
                "purchased-products": 2,
                "between": [
                    "2019-03-23T00:00:00Z",
                    "2019-04-11T00:00:00Z"
                ]
            }
        ]
    }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = {
    "filter": [
        {
            "country": {
                "equals": "USA"
            }
        }
    ],
    "query": [
        {
            "purchased-products": 2,
            "between": [
                "2019-03-23T00:00:00Z", 
                "2019-04-11T00:00:00Z"
            ]
        }
    ]
}

print(slicingdice.aggregation(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject queryData = new JSONObject()
        .put("filter", new JSONArray()
            .put(new JSONObject()
                .put("country", new JSONObject()
                    .put("equals", "USA"))))
        .put("query", new JSONArray()
            .put(new JSONObject()
                .put("purchased-products", 2)
                .put("between", new JSONArray()
                    .put("2019-03-23T00:00:00Z")
                    .put("2019-04-11T00:00:00Z"))));
        JSONObject result = slicingdice.aggregation(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = {
    "filter" => [
        {
            "country" => {
                "equals" => "USA"
            }
        }
    ],
    "query" => [
        {
            "purchased-products" => 2,
            "between" => [
                "2019-03-23T00:00:00Z", 
                "2019-04-11T00:00:00Z"
            ]
        }
    ]
}

puts slicingdice.aggregation(query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = {
    "filter": [
        {
            "country": {
                "equals": "USA"
            }
        }
    ],
    "query": [
        {
            "purchased-products": 2,
            "between": [
                "2019-03-23T00:00:00Z", 
                "2019-04-11T00:00:00Z"
            ]
        }
    ]
};

slicingdice.aggregation(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    "filter" => array(
        array(
            "country" => array(
                "equals" => "USA"
            )
        )
    ),
    "query" => array(
        array(
            "purchased-products" => 2,
            "between" => array(
                "2019-03-23T00:00:00Z", 
                "2019-04-11T00:00:00Z"
            )
        )
    )
);

print_r($slicingdice->aggregation($queryData));>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new Dictionary{
                {"filter", new List{
                    new Dictionary{
                        {"country", new Dictionary{
                            {"equals", "USA"}
                        }}
                    }
                }},
                {"query", new List{
                    new Dictionary{
                        {"purchased-products", 2},
                        {"between", new List{
                            "2019-03-23T00:00:00Z", 
                            "2019-04-11T00:00:00Z"
                        }}
                    }
                }}
            };

            var result = slicingdice.Aggregation(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := map[string]interface{}{
        "filter": []interface{}{
            map[string]interface{}{
                "country": map[string]interface{}{
                    "equals": "USA",
                },
            },
        },
        "query": []interface{}{
            map[string]interface{}{
                "purchased-products": 2,
                "between": []interface{}{
                    "2019-03-23T00:00:00Z",
                    "2019-04-11T00:00:00Z",
                },
            },
        },
    }

    fmt.Println(slicingdice.Aggregation(queryData))
}

Top values

You should use top values queries to know what are the most common values for a specific column and how many times was the value inserted.

In order to make a top values query, you need to send a POST request to https://api.slicingdice.com/v1/query/top_values with a single JSON object of queries identified by unique names as keys, each containing a column and a condition.

The query below will list what are the top 3 most common values for the column name , where the values contains the string eff on dimension users .

Dimension value

If you do not provide a value to dimension, the query will be performed over the default dimension.


SELECT
  name
FROM
  users
WHERE
  name LIKE '%eff%'
GROUP by
  name
LIMIT
  3;


$ curl -X POST https://api.slicingdice.com/v1/sql \
    -H 'Authorization: MASTER_OR_WRITE_API_KEY' \
    -H 'Content-Type: application/sql' \
    -d 'SELECT name FROM users WHERE name LIKE '%eff%' GROUP by name LIMIT 3;'


 curl -X POST https://api.slicingdice.com/v1/query/top_values \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
        "name-contains-eff": {
            "name": 3,
            "contains": [
                "eff"
            ],
            "dimension": "users"
        }
    }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = {
    "name-contains-eff": {
        "dimension": "users",
        "contains": [
            "eff"
        ],
        "name": 3
    }
}

print(slicingdice.top_values(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject queryData = new JSONObject()
        .put("name-contains-eff", new JSONObject()
            .put("dimension", "users")
            .put("contains", new JSONArray()
                .put("eff"))
            .put("name", 3));
        JSONObject result = slicingdice.topValues(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = {
    "name-contains-eff" => {
        "dimension" => "users",
        "contains" => [
            "eff"
        ],
        "name" => 3
    }
}

puts slicingdice.top_values(query_data)

const SlicingDice = require('slicerjs');

const usesTestEndpoint = true;
const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = {
    "name-contains-eff": {
        "dimension": "users",
        "contains": [
            "eff"
        ],
        "name": 3
    }
};

slicingdice.topValues(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    "name-contains-eff" => array(
        "dimension" => "users",
        "contains" => array(
            "eff"
        ),
        "name" => 3
    )
);

print_r($slicingdice->topValues($queryData));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new Dictionary{
                {"name-contains-eff", new Dictionary{
                    {"dimension", "users"},
                    {"contains", new List{
                        "eff"
                    }},
                    {"name", 3}
                }}
            };

            var result = slicingdice.TopValues(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := map[string]interface{}{
        "name-contains-eff": map[string]interface{}{
            "dimension": "users",
            "contains": []interface{}{
                "eff",
            },
            "name": 3,
        },
    }

    fmt.Println(slicingdice.TopValues(queryData))
}

Exists

You should use exists queries to check whether the given entity IDs exist within a dimension.

In order to make a exists query, you need to send a POST request to https://api.slicingdice.com/v1/query/exists/entity with a single JSON object containing a list of entity IDs (ids) and the dimension you want to check.

The query below will check if multiple users exists on the database MyProject .

The /exists endpoint expects a JSON object containing a list of entity IDs to be verified.

This query doesn't works at SlicingDice SQL

On our SQL Drivers and API endpoint, you can't make an exists query directly, you will need to check ID by ID to know if they exist on the database.


curl -X POST https://api.slicingdice.com/v1/query/exists/entity \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
        "ids": [
            "user1@slicingdice.com",
            "user2@slicingdice.com",
            "user3@slicingdice.com",
            "user4@slicingdice.com",
            "user5@slicingdice.com"
        ],
        "dimension": "users"
    }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

table = "dimension"
query_data = [
    "user1@slicingdice.com", 
    "user2@slicingdice.com", 
    "user3@slicingdice.com", 
    "user4@slicingdice.com", 
    "user5@slicingdice.com"
]

print(slicingdice.exists_entity(query_data, table=table))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        String table = "dimension";
        JSONArray queryData = new JSONArray()
        .put("user1@slicingdice.com")
        .put("user2@slicingdice.com")
        .put("user3@slicingdice.com")
        .put("user4@slicingdice.com")
        .put("user5@slicingdice.com");

        JSONObject result = slicingdice.existsEntity(queryData, table);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

table = "dimension"
query_data = [
    "user1@slicingdice.com", 
    "user2@slicingdice.com", 
    "user3@slicingdice.com", 
    "user4@slicingdice.com", 
    "user5@slicingdice.com"
]

puts slicingdice.exists_entity(query_data, table=table)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const table = "dimension";
const queryData = [
    "user1@slicingdice.com", 
    "user2@slicingdice.com", 
    "user3@slicingdice.com", 
    "user4@slicingdice.com", 
    "user5@slicingdice.com"
];

slicingdice.existsEntity(queryData, table).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;

$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$table = "dimension";
$queryData = array(
    "user1@slicingdice.com", 
    "user2@slicingdice.com", 
    "user3@slicingdice.com", 
    "user4@slicingdice.com", 
    "user5@slicingdice.com"
);

print_r($slicingdice->existsEntity($queryData, $table));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            string table = "dimension";
            var queryData = new List{
                "user1@slicingdice.com", 
                "user2@slicingdice.com", 
                "user3@slicingdice.com", 
                "user4@slicingdice.com", 
                "user5@slicingdice.com"
            };

            var result = slicingdice.ExistsEntity(queryData, table);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    table := "dimension"
    queryData := []string{
        "user1@slicingdice.com",
        "user2@slicingdice.com",
        "user3@slicingdice.com",
        "user4@slicingdice.com",
        "user5@slicingdice.com",
    }

    fmt.Println(slicingdice.ExistsEntity(queryData, table))
}

Total

You should use total queries to know how many unique entities you have in a particular dimension or on the entire database, considering all of its dimensions.

In order to make a total query, you need to send a POST request to https://api.slicingdice.com/v1/query/count/entity/total with a single JSON object containing a list of dimensions.

The query below will count how many unique entities there are on the entire database.

This query doesn't works at SlicingDice SQL

Our SQL Drivers and endpoint don't support a global count total on all dimensions at the same time. You will need to ask the count for each dimension per time.


SELECT
  COUNT([entity-id])
FROM
  users;
SELECT
  COUNT([entity-id])
FROM
  pages;


$ curl -X POST https://api.slicingdice.com/v1/sql \
    -H 'Authorization: MASTER_OR_WRITE_API_KEY' \
    -H 'Content-Type: application/sql' \
    -d 'SELECT
  COUNT([entity-id])
FROM
  users;
SELECT
  COUNT([entity-id])
FROM
  pages;'


curl -X POST https://api.slicingdice.com/v1/query/count/entity/total \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json'
    -d '{
        "dimensions": ["users", "pages"]
        }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = [
    "users", 
    "pages"
]

print(slicingdice.count_entity_total(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import java.util.ArrayList;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        ArrayList queryData = new ArrayList<>();
        queryData.add("users");
        queryData.add("pages");
      
        JSONObject result = slicingdice.countEntityTotal(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = [
    "users", 
    "pages"
]

puts slicingdice.count_entity_total(query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = [
    "users", 
    "pages"
];

slicingdice.countEntityTotal(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    "users", 
    "pages"
);

print_r($slicingdice->countEntityTotal($queryData));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new List{
                "users", 
                "pages"
            };

            var result = slicingdice.CountEntityTotal(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := []string{}{
        "users",
        "pages",
    }

    fmt.Println(slicingdice.CountEntityTotal(queryData))
}

Result

The result data extraction allows you to retrieve data from columns you have stored. You can use it to answer many questions, like the ones mentioned below. When using the result query, you select the entities that will be retrieved with the query parameter, exactly the same as a count entities query. Next, you define which columns you want to retrieve data from with the columns parameter.

In order to make a result data extraction query, you need to send a POST request to https://api.slicingdice.com/v1/data_extraction/result with a single JSON object containing your query and columns you want to retrieve from the database.

The query below will retrieve the city and state from users that are either from NY or CA.

Result queries by default retrieve 100 entities

By default the result query brings 100 records. If you want to return more than it you should define a "limit" clause with the number of records you want to retrieve, just like this example: {"columns": "all", "limit": 1000}


SELECT
  city,
  state
FROM
  default
WHERE
  state = 'NY'
  or state = 'CA';


$ curl -X POST https://api.slicingdice.com/v1/sql \
    -H 'Authorization: MASTER_OR_WRITE_API_KEY' \
    -H 'Content-Type: application/sql' \
    -d 'SELECT city, state FROM default WHERE state = 'NY' or state = 'CA';'


curl -X POST https://api.slicingdice.com/v1/data_extraction/result \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
        "query": [
            {
                "state": { "equals": "NY" }
            },
            "or",
            {
                "state": { "equals": "CA" }
            }
        ],
        "columns": [
            "city",
            "state"
        ],
        "limit": 3
    }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }
    ],
    "limit": 3,
    "columns": [
        "city", 
        "state"
    ]
}

print(slicingdice.result(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject queryData = new JSONObject()
        .put("query", new JSONArray()
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "NY")))
            .put("or")
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "CA"))))
        .put("limit", 3)
        .put("columns", new JSONArray()
            .put("city")
            .put("state"));
        JSONObject result = slicingdice.result(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = {
    "query" => [
        {
            "state" => {
                "equals" => "NY"
            }
        }, 
        "or", 
        {
            "state" => {
                "equals" => "CA"
            }
        }
    ],
    "limit" => 3,
    "columns" => [
        "city", 
        "state"
    ]
}

puts slicingdice.result(query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }
    ],
    "limit": 3,
    "columns": [
        "city", 
        "state"
    ]
};

slicingdice.result(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    "query" => array(
        array(
            "state" => array(
                "equals" => "NY"
            )
        ), 
        "or", 
        array(
            "state" => array(
                "equals" => "CA"
            )
        )
    ),
    "limit" => 3,
    "columns" => array(
        "city", 
        "state"
    )
);

print_r($slicingdice->result($queryData));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new Dictionary{
                {"query", new List{
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "NY"}
                        }}
                    }, 
                    "or", 
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "CA"}
                        }}
                    }
                }},
                {"limit", 3},
                {"columns", new List{
                    "city", 
                    "state"
                }}
            };

            var result = slicingdice.Result(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := map[string]interface{}{
        "query": []interface{}{
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "NY",
                },
            },
            "or",
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "CA",
                },
            },
        },
        "limit": 3,
        "columns": []interface{}{
            "city",
            "state",
        },
    }

    fmt.Println(slicingdice.Result(queryData))
}

Score

The score data extraction allows you to retrieve data while receiving the entities' relative importance given a set of conditions.

For example, you may want to get the email addresses of your users according to their purchase behavior and know the most important ones.

When using the score query, you select the entities that will be retrieved with the query parameter, exactly the same as a count entities query. Next, you define which columns you want to retrieve data from with the columns parameter.

In order to make a score query, you need to send a POST request to https://api.slicingdice.com/v1/data_extraction/score with a single JSON object containing your query and columns you want to retrieve from SlicingDice.

The query below will retrieve only the city and state from users that are either from NY or CA , or male , or have between 18 and 25 years old.

Score total has a limit of 100 entities

To optimize the all entities operation, the database limits the total result to 100 entities. You still can specify the limit, but it should be lower than or equal to 100.


SELECT
  city,
  state,
  score
FROM
  default
WHERE
  state = 'NY'
  or state = 'CA'
  or gender = 'male'
  or age BETWEEN 17
  AND 25;


$ curl -X POST https://api.slicingdice.com/v1/sql \
    -H 'Authorization: MASTER_OR_WRITE_API_KEY' \
    -H 'Content-Type: application/sql' \
    -d 'SELECT city, state, score FROM default WHERE state = 'NY' or state = 'CA' or gender = 'male' or age BETWEEN 17 AND 25;'


curl -X POST https://api.slicingdice.com/v1/data_extraction/score \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
        "query": [
            {
                "state": { "equals": "NY" }
            },
            "or",
            {
                "state": { "equals": "CA" }
            },
            "or",
            {
                "gender": { "equals": "male" }
            },
            "or",
            {
                "age": { "range": [ 18, 25 ] }
            }
        ],
        "columns": [
            "city",
            "state"
        ],
        "limit": 3
    }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }, 
        "or", 
        {
            "gender": {
                "equals": "male"
            }
        }, 
        "or", 
        {
            "age": {
                "range": [
                    18, 
                    25
                ]
            }
        }
    ],
    "limit": 3,
    "columns": [
        "city", 
        "state"
    ]
}

print(slicingdice.score(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject queryData = new JSONObject()
        .put("query", new JSONArray()
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "NY")))
            .put("or")
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "CA")))
            .put("or")
            .put(new JSONObject()
                .put("gender", new JSONObject()
                    .put("equals", "male")))
            .put("or")
            .put(new JSONObject()
                .put("age", new JSONObject()
                    .put("range", new JSONArray()
                        .put(18)
                        .put(25)))))
        .put("limit", 3)
        .put("columns", new JSONArray()
            .put("city")
            .put("state"));
        JSONObject result = slicingdice.score(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = {
    "query" => [
        {
            "state" => {
                "equals" => "NY"
            }
        }, 
        "or", 
        {
            "state" => {
                "equals" => "CA"
            }
        }, 
        "or", 
        {
            "gender" => {
                "equals" => "male"
            }
        }, 
        "or", 
        {
            "age" => {
                "range" => [
                    18, 
                    25
                ]
            }
        }
    ],
    "limit" => 3,
    "columns" => [
        "city", 
        "state"
    ]
}

puts slicingdice.score(query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }, 
        "or", 
        {
            "gender": {
                "equals": "male"
            }
        }, 
        "or", 
        {
            "age": {
                "range": [
                    18, 
                    25
                ]
            }
        }
    ],
    "limit": 3,
    "columns": [
        "city", 
        "state"
    ]
};

slicingdice.score(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    "query" => array(
        array(
            "state" => array(
                "equals" => "NY"
            )
        ), 
        "or", 
        array(
            "state" => array(
                "equals" => "CA"
            )
        ), 
        "or", 
        array(
            "gender" => array(
                "equals" => "male"
            )
        ), 
        "or", 
        array(
            "age" => array(
                "range" => array(
                    18, 
                    25
                )
            )
        )
    ),
    "limit" => 3,
    "columns" => array(
        "city", 
        "state"
    )
);

print_r($slicingdice->score($queryData));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new Dictionary{
                {"query", new List{
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "NY"}
                        }}
                    }, 
                    "or", 
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "CA"}
                        }}
                    }, 
                    "or", 
                    new Dictionary{
                        {"gender", new Dictionary{
                            {"equals", "male"}
                        }}
                    }, 
                    "or", 
                    new Dictionary{
                        {"age", new Dictionary{
                            {"range", new List{
                                18, 
                                25
                            }}
                        }}
                    }
                }},
                {"limit", 3},
                {"columns", new List{
                    "city", 
                    "state"
                }}
            };

            var result = slicingdice.Score(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := map[string]interface{}{
        "query": []interface{}{
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "NY",
                },
            },
            "or",
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "CA",
                },
            },
            "or",
            map[string]interface{}{
                "gender": map[string]interface{}{
                    "equals": "male",
                },
            },
            "or",
            map[string]interface{}{
                "age": map[string]interface{}{
                    "range": []interface{}{
                        18,
                        25,
                    },
                },
            },
        },
        "limit": 3,
        "columns": []interface{}{
            "city",
            "state",
        },
    }

    fmt.Println(slicingdice.Result(Score))
}

Paginating

In data extraction queries (result and score), the result message may be really large when returning a large number of stored data. Instead of dumping everything in only one message, in this situation, the database uses pagination in order to guarantee your request will be fulfilled fast, whenever more than 1,024 entities will be returned. When pagination occurs, the result data is sent in more than one message, one page per time containing at most 1,024 entities. The message also contains three extra parameters:

page: indicates the page number for the last request;
next-page: informs whether there are more pages;
page-token: the token related to that page.

SQL Drivers

Our SQL Drivers will deal with pagination for you so you don't need to worry about it. If you're exclusively using our SQL Drivers, feel free to skip this guide.

The code below is an example of a paginated response:

{
    "status": "success",
    "data": {
        "customer1@mycustomer.com": {
            "state": "NY"
        },
        "customer1@mycustomer.com": {
            "state": "CA"
        },
        "customer3@mycustomer.com": {
            "state": "CA"
        },
        # ... truncated for visibility purposes
        "customer1023@mycustomer.com": {
            "state": "CA"
        },
        "customer1024@mycustomer.com": {
            "state": "CA"
        }
    },
    "page": 1,
    "next-page": 2,
    "page-token": "azbUmcDkWwjlqxKOh253",
    "took": 0.103
}

In order to request the next page of results, use the received page-token in the same request.

If the content of next-page is null, we know there are no more pages to retrieve. The last paginated message doesn't even contain a page-token parameter.

Geolocation

The queries on geolocation columns allows you to retrieve geolocation data from your databases.

In order to query geolocation columns, you can use the following endpoints:

POST /data_extraction/result: Result queries
POST /data_extraction/score: Score queries
POST query/count/entity (except Near query): Count entities queries
POST query/count/event (except Near query): Cont events queries
POST /query/aggregation (except Near query): Aggregation queries
POST /top_values(except Near query): Top values queries

The query below will list what are the top 3 most common values for the column name , where the values contains the string eff on dimension users .

SQL not supported (yet)

Queries in geolocation columns are not supported on SQL endpoint and drivers, but we are working to support these new queries. As for now, you can only use the API or our Client Libraries and SDKs.

Geolocation query structure

The geolocation columns can have three special queries types: bounding box, radius and near. Each one is described in the next sections.

Bounding Box

The bounding box query receives two main parameters: lower-left and upper-right. Each one of these parameters will receive a longitude and latitude parameters to establish a rectangular search area based on a diagonal.

To create a bounding box query, you'll need to use the geo_bounding_box predicate and the following parameters:

lower-left: This parameter will receive another two parameters: a longitude and latitude to establish the lower left corner of the rectangular search area.
upper-right: This parameter will receive another two parameters: a longitude and latitude to establish the upper right corner of the rectangular search area.

This query will return all entities found in the query area. The following example image shows the search area of the bounding box on a map. The black points are the given coordinates, the rectangle is the search area and the red circles are the results.

Search area of the bounding box on a map. The black points are the given coordinates, the rectangle is the search area and the red circles are the results.

Radius

The radius query receives a longitude and latitude parameter to establish the center of a radius and a distance limiter to find entities.

To create a radius query, you'll need to use the geo_radius predicate and the following parameters:

longitude: The specified longitude of the radius center. Accepted values are between -180 and 180.
latitude: The specified latitude of the radius center. Accepted values are between -90 and 90.
unit: The metric of the radius. Accepted values are: km (kilometer), m (meter), mi (mile) and deg (degree).
radius: The radius that you want to query. The metric is based on the unit parameter.

This query will return all entities found in the query area. The following example image shows how the search area of a radius works on a map. The center circle is the given coordinates, the bigger circle is the search area and the red circles are the results.

Search area of a radius works on a map. The center circle is the given coordinates, the bigger circle is the search area and the red circles are the results.

Near (k-nearest neighbors)

The near (k-nearest neighbors) query receives a longitude and latitude parameters, a limit parameter and a show_coordinates parameter to search the nearest geolocation entities based on given coordinates.

To create a near query, you'll need to use the geo_near predicate and the following parameters:

longitude: The specified longitude of the radius center. Accepted values are between -180 and 180.
latitude: The specified latitude of the radius center. Accepted values are between -90 and 90.
limit: How many entities must be returned.
show_coordinates: If the query must return the coordinates of the found entities. Accepted values are: true or false (default).

This query will return all nearest entities found and the distance, in kilometers, of the specified coordinates and entity-found coordinates.

If show_coordinates was set to true, a column named column-name_coordinates (where column-name is the name of the queried column) will be shown with the coordinates of the found entity.

The following example image shows how near query works on a map. The red point is the given coordinates and the blue points are the results.

Retrieving geolocation entities based on a radius

What are the gym academies at a distance of 5km from my home?


url -X POST https://api.slicingdice.com/v1/data_extraction/result \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
         "query":[
            {
               "gym-academies":{
                  "geo_radius":{
                     "longitude":160.5,
                     "latitude":-38.9,
                     "radius":5,
                     "unit":"km"
                  }
               }
            }
         ]
      }'

Retrieving nearest geolocation entities

What are the 5 nearest pizzerias from my office?


curl -X POST https://api.slicingdice.com/v1/data_extraction/result \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
         "query":[
            {
               "pizza-location":{
                  "geo_near":{
                     "longitude":52.89566,
                     "latitude":80.32578,
                     "limit":5
                  }
               }
            }
         ],
         "show-coordinates":true
      }'

Retrieving geolocation entities based on a bounding box

What devices are registered in a block?


curl -X POST https://api.slicingdice.com/v1/data_extraction/result \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
         "query":[
            {
               "devices":{
                  "geo_bounding_box":{
                     "lower-left":{
                        "longitude":50,
                        "latitude":30
                     },
                     "upper-right":{
                        "longitude":160,
                        "latitude":80
                     }
                  }
               }
            }
         ]
      }'

Retrieving geolocation entities based on events, using the bounding box

How many devices were registered in the block in January 2019?


curl -X POST https://api.slicingdice.com/v1/query/count/event \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
         "query":[
            {
               "devices":{
                  "geo_bounding_box":{
                     "lower-left":{
                        "longitude":50,
                        "latitude":30
                     },
                     "upper-right":{
                        "longitude":160,
                        "latitude":80
                     },
                     "between":[
                        "2019-01-01T00:00:00Z",
                        "2019-01-31T00:00:00Z"
                     ]
                  }
               }
            }
         ]
      }'

Saved queries

This section will explain only JSON particularities. If you're using a SQL Driver you can skip this guide. Saved queries are here to help you re-using those queries that you repeat over and over again. Instead of re-writing the entire query whenever you need to analyze your data, save it and call by name.

SQL Drivers

Currently, saved queries are not supported on our SQL Drivers.

Saving a query

In order to save a query, send a POST request to https://api.slicingdice.com/v1/query/saved request containing a name , its type and the query itself.

Since the name will be used in the query URL, only alphanumeric a-zA-Z0-9 and - characters are allowed.

The type parameter accepts the following values determining to which query will be executed:

count/entity: Count entities queries
count/event: Count events queries
aggregation: Aggregation queries
top_values: Top values queries
exists/entity: Exists queries
count/entity/total: Total queries

The request below shows how to save a query.


 curl -X POST https://api.slicingdice.com/v1/query/saved \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
        "name": "my-saved-query",
        "type": "count/entity",
        "query": [
            {
                "state": {
                    "equals": "NY"
                }
            },
            "or",
            {
                "state": {
                    "equals": "CA"
                }
            }
        ],
        "dimension": "users"
    }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }
    ],
    "type": "count/entity",
    "name": "my-saved-query",
    "dimension": "users"
}

print(slicingdice.create_saved_query(query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject queryData = new JSONObject()
        .put("query", new JSONArray()
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "NY")))
            .put("or")
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "CA"))))
        .put("type", "count/entity")
        .put("name", "my-saved-query")
        .put("dimension", "users");
        JSONObject result = slicingdice.createSavedQuery(queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = {
    "query" => [
        {
            "state" => {
                "equals" => "NY"
            }
        }, 
        "or", 
        {
            "state" => {
                "equals" => "CA"
            }
        }
    ],
    "type" => "count/entity",
    "name" => "my-saved-query",
    "dimension" => "users"
}

puts slicingdice.create_saved_query(query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }
    ],
    "type": "count/entity",
    "name": "my-saved-query",
    "dimension": "users"
};

slicingdice.createSavedQuery(queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    "query" => array(
        array(
            "state" => array(
                "equals" => "NY"
            )
        ), 
        "or", 
        array(
            "state" => array(
                "equals" => "CA"
            )
        )
    ),
    "type" => "count/entity",
    "name" => "my-saved-query",
    "dimension" => "users"
);

print_r($slicingdice->createSavedQuery($queryData));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new Dictionary{
                {"query", new List{
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "NY"}
                        }}
                    }, 
                    "or", 
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "CA"}
                        }}
                    }
                }},
                {"type", "count/entity"},
                {"name", "my-saved-query"},
                {"dimension", "users"}
            };

            var result = slicingdice.CreateSavedQuery(queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := map[string]interface{}{
        "query": []interface{}{
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "NY",
                },
            },
            "or",
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "CA",
                },
            },
        },
        "type": "count/entity",
        "name": "my-saved-query",
        "dimension": "users",
    }

    fmt.Println(slicingdice.CreateSavedQuery(queryData))
}

Listing saved queries

Great, query is now saved. We can check all our saved queries with a simple GET /query/saved request like this example below.


 curl -X GET https://api.slicingdice.com/v1/query/saved \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

print(slicingdice.get_saved_queries())

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject result = slicingdice.getSavedQueries();
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

puts slicingdice.get_saved_queries()

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

slicingdice.getSavedQueries().then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

print_r($slicingdice->getSavedQueries());
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var result = slicingdice.GetSavedQueries();
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    fmt.Println(slicingdice.GetSavedQueries())
}

Using saved queries

In order to use a saved query, issue a GET query/saved/my-saved-query request, where my-saved-query must be replaced by the name given to the query on its creation.


 curl -X GET https://api.slicingdice.com/v1/query/saved/my-saved-query \
    -H 'Authorization: MASTER_OR_READ_API_KEY' \
    -H 'Content-Type: application/json'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

print(slicingdice.get_saved_query("my-saved-query"))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject result = slicingdice.getSavedQuery("my-saved-query");
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

puts slicingdice.get_saved_query("my-saved-query")

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

slicingdice.getSavedQueries("my-saved-query").then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

print_r($slicingdice->getSavedQuery("my-saved-query"));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var result = slicingdice.GetSavedQuery("my-saved-query");
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    fmt.Println(slicingdice.GetSavedQuery("my-saved-query"))
}

Updating saved queries

In order to update a saved query, issue a PUT query/saved/my-saved-query request, where my-saved-query must be replaced by the name given to the query on its creation.

Be advised that you CAN'T modify a saved query name.


curl -X PUT https://api.slicingdice.com/v1/query/saved/my-saved-query \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json' \
    -d '{
        "type": "count/entity",
        "query": [
            {
                "state": {
                    "equals": "NY"
                }
            },
            "or",
            {
                "state": {
                    "equals": "CA"
                }
            }
        ],
        "dimension": "users"
    }'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

query_data = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }
    ],
    "type": "count/entity",
    "dimension": "users"
}

print(slicingdice.update_saved_query("my-saved-query", query_data))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject queryData = new JSONObject()
        .put("query", new JSONArray()
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "NY")))
            .put("or")
            .put(new JSONObject()
                .put("state", new JSONObject()
                    .put("equals", "CA"))))
        .put("type", "count/entity")
        .put("dimension", "users");
      
        JSONObject result = slicingdice.updateSavedQuery("my-saved-query", queryData);
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

query_data = {
    "query" => [
        {
            "state" => {
                "equals" => "NY"
            }
        }, 
        "or", 
        {
            "state" => {
                "equals" => "CA"
            }
        }
    ],
    "type" => "count/entity",
    "dimension" => "users"
}

puts slicingdice.update_saved_query("my-saved-query", query_data)

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

const queryData = {
    "query": [
        {
            "state": {
                "equals": "NY"
            }
        }, 
        "or", 
        {
            "state": {
                "equals": "CA"
            }
        }
    ],
    "type": "count/entity",
    "dimension": "users"
};

slicingdice.updateSavedQuery("my-saved-query", queryData).then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

$queryData = array(
    "query" => array(
        array(
            "state" => array(
                "equals" => "NY"
            )
        ), 
        "or", 
        array(
            "state" => array(
                "equals" => "CA"
            )
        )
    ),
    "type" => "count/entity",
    "dimension" => "users"
);

print_r($slicingdice->updateSavedQuery("my-saved-query", $queryData));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var queryData = new Dictionary{
                {"query", new List{
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "NY"}
                        }}
                    }, 
                    "or", 
                    new Dictionary{
                        {"state", new Dictionary{
                            {"equals", "CA"}
                        }}
                    }
                }},
                {"type", "count/entity"},
                {"dimension", "users"}
            };

            var result = slicingdice.UpdateSavedQuery("my-saved-query", queryData);
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    queryData := map[string]interface{}{
        "query": []interface{}{
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "NY",
                },
            },
            "or",
            map[string]interface{}{
                "state": map[string]interface{}{
                    "equals": "CA",
                },
            },
        },
        "type": "count/entity",
        "dimension": "users",
    }

    fmt.Println(slicingdice.UpdateSavedQuery("my-saved-query", queryData))
}

Deleting saved queries

In order to delete a saved query, issue a DELETE query/saved/my-saved-query request, where my-saved-query must be replaced by the name given to the query on its creation.


 curl -X DELETE https://api.slicingdice.com/v1/query/saved/my-saved-query \
    -H 'Authorization: MASTER_API_KEY' \
    -H 'Content-Type: application/json'

from pyslicer import SlicingDice
slicingdice = SlicingDice(master_key='MASTER_API_KEY')

print(slicingdice.delete_saved_query("my-saved-query"))

import com.slicingdice.jslicer.SlicingDice;
import java.io.IOException;
import org.json.JSONObject;
import org.json.JSONArray;

public class Example {
    public static void main(String[] args) throws IOException {
        SlicingDice slicingdice = new SlicingDice("MASTER_API_KEY");

        JSONObject result = slicingdice.deleteSavedQuery("my-saved-query");
        System.out.println(result.toString());
    }
}

require 'rbslicer'
slicingdice = SlicingDice.new(master_key: 'MASTER_API_KEY')

puts slicingdice.delete_saved_query("my-saved-query")

const SlicingDice = require('slicerjs');

const slicingdice = new SlicingDice({masterKey: 'MASTER_API_KEY'});

slicingdice.deleteSavedQuery("my-saved-query").then((resp) => {
    console.log(resp);
}, (err) => {
    console.error(err);
});

<?php
use Slicer\SlicingDice;
$slicingdice = new SlicingDice(array("masterKey" => "MASTER_API_KEY"));

print_r($slicingdice->deleteSavedQuery("my-saved-query"));
?>

using System.Collections.Generic;
using Slicer;
using Newtonsoft.Json;

namespace SlicerTester.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            var slicingdice = new SlicingDice(masterKey: "MASTER_API_KEY");

            var result = slicingdice.DeleteSavedQuery("my-saved-query");
            System.Console.WriteLine(JsonConvert.SerializeObject(result).ToString());
        }
    }
}

package main
import (
    "fmt"
    "github.com/SlicingDice/slicingdice-go/slicingdice"
)
func main() {
    keys := new(slicingdice.APIKey)
    keys.MasterKey = "MASTER_API_KEY"
    slicingdice := slicingdice.New(keys, 60)

    fmt.Println(slicingdice.DeleteSavedQuery("my-saved-query"))
}