How to filter and facet by properties of a json field?

General Discussions - English
1

Hi everyone,

I’m new to manticore search and I meet a scenario that I don’t know how to go.
I have a table stores judgement documents. The table is created like this:

create table judgements(
	id BIGINT,
	case_number TEXT,
	case_date TIMESTAMP,
	...
	parties JSON
) engine='columnar';

“parties” field is an array consists with some properties like this:

[
    {
        "name": "entity_a",
        "ent_id": 12345,
        "role": "accuser",
        "result": "win"
    },
    {
        "name": "entity_b",
        "ent_id": 67890,
        "role": "defendant",
        "result": "loss"
    }
]

I’m now looking for the detail of a certain entity, say ent_id=12345, and trying to find the recent 10 docs that the entity is involved as a defenant. Meanwhile I’d like to know all the count of different results.
I succeeded in the prior part with the sql:

select *, any(x.ent_id=12345 and x.role='defendant' for x in parties) cond from judgements where cond = 1 order by case_date desc limit 10;

I don’t have an idea on the facet by parties.result part. So anyone could give a help?
In addition, I’d like to conduct a json-formatted search request to get the same result. How should I compose the json data?

Thanks in advance.

2

why not just add FACET by the attr you needed? like this

select *, any(x.ent_id=12345 and x.role='defendant' for x in parties) cond from judgements where cond = 1 order by case_date desc FACET parties.result limit 10;
3

Hi Tomat,

Thanks for the answer. However, it doesn’t show the expected result:

+----------------+----------+
| parties.result | count(*) |
+----------------+----------+
| NULL           |      823 |
+----------------+----------+

What I need is something like this:

win         10
loss         3
withdraw     2