About JSON attributes in Manticore Search

Support of JSON attributes in Manticore Search allows storing JSON objects in a table, using them in filtering, sorting or grouping and returning them in the query response. JSON attributes are available to both plain and RealTime tables and, like other attributes, they can't be used in MATCH() clauses. In the case of plain tables, JSON objects are expected to be a plain text column that will be parsed at indexing.

In this course we will use a RealTime table in which we insert several documents containing JSON objects.

table testjson
{
    type = rt
    path = /usr/local/sphinx/data/testjson
    rt_field = name
    rt_attr_string = name
    rt_attr_json = metadata
}

Inserting documents with JSON attributes

First, let's connect to Manticore using linux mysql client:

mysql -P9306 -h0

We are going to use a simple document with id, name and a metadata attribute representing a product. The metadata can encapsulate any information about the product, schema-less.

{
  "locations": [
    {
      "name": "location1",
      "lat": 23.000000,
      "long": 46.500000,
      "stock": 30
    },
    {
      "name": "location2",
      "lat": 24.000000,
      "long": 47.500000,
      "stock": 20
    },
    {
      "name": "location3",
      "lat": 24.500000,
      "long": 47.500000,
      "stock": 10
    }
  ],
  "color": [
    "blue",
    "black",
    "yellow"
  ],
  "price": 210.00,
  "cpu": {
    "model": "Kyro 345",
    "cores": 8,
    "chipset": "snapdragon 845"
  },
  "memory": 128
}

Now let's insert several products:

INSERT INTO testjson VALUES(1,'Product One','{"locations":[{"lat":23.000000,"long":46.500000,"stock":30},{"lat":24.000000,"long":47.500000,"stock":20},{"lat":24.500000,"long":47.500000,"stock":10}],"color":["blue","black","yellow"],"price":210.00,"cpu":{"model":"Kyro 345","cores":8,"chipset":"snapdragon 845"},"video_rec":[1080,720],"memory":32}');

INSERT INTO testjson VALUES(2,'Product Two','{"locations":[{"lat":23.100000,"long":46.600000,"stock":10},{"lat":24.000000,"long":47.500000,"stock":0},{"lat":24.300000,"long":47.550000,"stock":10}],"color":["white","black","blue"],"price":410.00,"cpu":{"model":"Cortex A75","cores":8,"chipset":"Exynos"},"video_rec":[2016,1080],"memory":128}');

INSERT INTO testjson VALUES(3,'Product Three','{"locations":[{"lat":23.100000,"long":46.600000,"stock":0},{"lat":24.000000,"long":47.500000,"stock":0},{"lat":24.300000,"long":47.550000,"stock":0}],"color":["black"],"price":360.00,"cpu":{"model":"Cortex A53","cores":8,"chipset":"Exynos"},"video_rec":[2016,1080],"memory":64}');

INSERT INTO testjson VALUES(4,'Product Four','{"locations":[{"lat":23.100000,"long":46.600000,"stock":4},{"lat":24.000000,"long":47.500000,"stock":11}],"price":380.00,"cpu":{"model":"A11","cores":6,"chipset":"Custom"},"video_rec":[2016,1080],"memory":64}');

JSON in searches

One important thing to keep in mind is that when a JSON property is used in a comparison operations (that can be filtering or sorting), the engine will try to guess the type of the property, but its guess might be not correct. So it's best to use the type casting functions (https://manual.manticoresearch.com/Functions/Type_casting_functions#Type-casting-functions).

In previous step one of the properties was 'price'. Let's perform a simple filtering:

SELECT * FROM testjson WHERE metadata.price>250;

or with explicit type definition:

SELECT * FROM testjson WHERE DOUBLE(metadata.price)>250;

Or we can do a string matching:

SELECT * FROM testjson WHERE metadata.cpu.model='Cortex A53';

REGEX() is supported too:

SELECT * FROM testjson WHERE REGEX(metadata.cpu.model, 'Cortex A.*');

On JSON attributes there are several special functions that can be used to iterate array: ANY (returning 1 if there was at least a match in the array), ALL (returning 1 when all items in the array obey the condition) or INDEXOF (returning position of the first match).

SELECT id, ANY(x.stock > 0 AND GEODIST(23.0,46.5, DOUBLE(x.lat), DOUBLE(x.long), {out=mi}) < 10 FOR x IN metadata.locations) AS close_to_you FROM testjson ORDER BY close_to_you DESC;

In our metadata we have a list of locations where the product can be sold. In the query above we first show products that have at least a location with the product in stock and which are within the range of a location of our own. The above query returns all products, but for each it calculates if it's available in a location within 10 miles away from you and returns 1 if so.

Grouping can be done as well against JSON properties:

SELECT metadata.memory, count(*) FROM testjson GROUP BY metadata.memory;

We can also group on a specific item of an array, like:

SELECT metadata.video_rec[0] as g, count(*) from testjson GROUP BY g;

Or we can sort by it:

SELECT * from testjson ORDER BY INTEGER(metadata.video_rec[0]) DESC;

Arrays containing strings can be filtered with IN function:

SELECT *,IN(metadata.color,'black','white') AS color_filter FROM testjson WHERE color_filter=1;

Faceting can be performed on single value properties or on array values:

SELECT * FROM testjson FACET metadata.video_rec FACET metadata.color;

As we can see in the result of the second facet, we have a NULL value because one of the documents misses the color property.

JSON properties support testing for non-existing value:

SELECT * FROM testjson WHERE metadata.color IS NULL;