Home  //  Play

Working with JSON attributes

Difficulty: Beginner
Estimated Time: 10 minutes

Manticoresearch - Working with JSON attributes.

In this course you will learn how to use JSON attributes in Manticore Search.

Working with JSON attributes

Step 1 of 3

About JSON attributes in Manticore Search

Support of JSON attributes in Manticore Search allows to store JSON objects in an index, use them in filtering, sorting or grouping and return in the query response. JSON attributes are available to both plain and RealTime indexes and like other attributes they can't be used in MATCH() clauses. In case of plain indexes, JSON objects are expected to be a plain text column which will be parsed at indexing.

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

index 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 meta data attribute representing a product. The meta data 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','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','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','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','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 conversion functions (https://docs.manticoresearch.com/latest/html/searching/expressions,_functions,_and_operators.html#type-conversion-functions).

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

SELECT * FROM testjson WHERE DOUBLE(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;