Querying JSON Fields for a Value Regardless of Key

General Discussions - English
1

I need assistance with querying records where a specific value is present within a JSON field. Given the following log records:

Record 18:

  • id: 2452683739294072961
  • message: Task completed successfully
  • level: 500
  • datetime: 1719394640
  • level_name: ERROR
  • extra: {“dummy”:“hi”,“dummy2”:“bye”}
  • context: {“scid”:“2”,“t”:“2”}

Record 19:

  • id: 2452683739294072940
  • message: Initialized application
  • level: 300
  • datetime: 1719394619
  • level_name: DEBUG
  • extra: {“dummy”:“hi”,“dummy2”:“bye”}
  • context: {“scid”:“13”,“t”:“2”}
mysql> desc table;
+---------------+-----------+----------------+
| Field         | Type      | Properties     |
+---------------+-----------+----------------+
| id            | bigint    |                |
| message       | text      | indexed stored |
| level         | uint      |                |
| datetime      | timestamp |                |
| level_name    | string    |                |
| extra         | json      |                |
| context       | json      |                |
+---------------+-----------+----------------+

I know that to retrieve all records where context.scid equals 13, I would use the query:

SELECT * FROM table WHERE context.scid = ‘13’;

However, I need to perform a search to find records where the number 13 appears anywhere within the context field, regardless of the key. Could you please advise on the best approach to accomplish this?

Thank you for your assistance.

2

I sure the query should be similar to this case Help with Filtering values in a Subquery with Manticore - #10 by tomat

SELECT *,  ANY((int(x) = 13) FOR x IN context) AS cond FROM table where cond=1;
3

Hi tomat,
Thank you so much for taking the time to respond to my question.

context: {“scid”:“2”,“t”:“2”}

The context field is a JSON object rather than an array of JSON objects. I am uncertain whether the ‘IN’ operator functions within a JSON context.

4

I am uncertain whether the ‘IN’ operator functions within a JSON context.

I’m afraid it doesn’t:

mysql> drop table if exists t; create table t(j json); insert into t values(1, '{"a": 1}'); select any(bigint(x)=1 for x in j) from t;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t(j json)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t values(1, '{"a": 1}')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select any(bigint(x)=1 for x in j) from t
--------------

+-----------------------------+
| any(bigint(x)=1 for x in j) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---
5

Hi sergey,

I have a specific query regarding data retrieval from manticore database. Currently, I use the following SQL query to fetch records:

SELECT * FROM table WHERE context.scid = 13;

I am exploring the possibility of specifying the value (in this case, 13) without explicitly stating the key (context.scid), similar to how it’s done in Elasticsearch.

Is there a way to achieve this in manticore ?

6

Please provide an example of how it’s done in Elasticsearch?

7

This article was generated by ChatGPT, an AI language model developed by OpenAI.

Example in Elasticsearch

Assume you have an index named my_index with documents like this:

{
  "_index": "my_index",
  "_type": "_doc",
  "_id": "1",
  "_source": {
    "context": {
      "scid": 13,
      "other_field": "value"
    },
    "another_field": "another_value"
  }
}

Query Example

You can search for documents where the value 13 is present in any field without specifying the key explicitly using a query string query:

GET my_index/_search
{
  "query": {
    "query_string": {
      "query": "13"
    }
  }
}

In this example, the query will search for the value 13 across all fields in the my_index index.

More Specific Query

If you want to search for 13 specifically within the context object, you can narrow it down slightly:

GET my_index/_search
{
  "query": {
    "query_string": {
      "query": "context.scid:13"
    }
  }
}

Using a match Query

For more structured searches, you can use a match query. This requires specifying the field, but it’s useful to see how Elasticsearch can handle it:

GET my_index/_search
{
  "query": {
    "match": {
      "context.scid": 13
    }
  }
}

Summary

  • Query String Query: Allows you to search for a value across all fields or within a specific object without needing to explicitly state the key for each search.
  • Match Query: Requires specifying the field but is useful for structured and more precise searches.

These Elasticsearch examples showcase how flexible queries can be structured without needing to specify each key explicitly, providing a way to search across multiple fields or specific objects more efficiently.

8

I see. Thanks. 2 differences here compared to Elasticsearch:

  1. Manticore doesn’t use inverted index for non-textual fields
  2. Elasticsearch flattens:
  "context": {
    "scid": 13,
    "other_field": "value"
  },

to

      "context": {
        "properties": {
          "other_field": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "scid": {
            "type": "long"
          }
        }
      }

while Manticore leaves it just as a json attribute:

| context       | json   |                |

I’ll discuss these items with the team.

9

The related issue is Index attributes in an inverted index · Issue #2449 · manticoresoftware/manticoresearch · GitHub