How to make correct json query on json arrays column?

Data Service
1

I have data table with json type column, that stores json array for each object.

json array structure is:

"categories": [
			{
				"id": 62,
				"name": "Интернет-магазины",
				"parent": null,
				"language": "ru"
			},
			{
				"id": 65,
				"name": "Цифровая и бытовая техника",
				"parent": {
					"id": 62,
					"name": "Интернет-магазины",
					"parent": null,
					"language": "ru"
				},
				"language": "ru"
			},
			{
				"id": 66,
				"name": "Мебель и товары для дома",
				"parent": {
					"id": 62,
					"name": "Интернет-магазины",
					"parent": null,
					"language": "ru"
				},
				"language": "ru"
			}
		]

I would like to make query to this table, and get objects where, for example, category id = 65, or objects where parent.id = 62

I’ve tested various queries, but nothing works, query
categories->'$[*].id'=62
or
categories->'$[*][*].parent'=62
returning empty object

Is it possible to make such query?

2

In order to understand the behaviour of the json paths and the results, try to put your request into the property, like: categories->'$[*].id' as ids
And you will see, that the result type is an JSON ARRAY which is cannot be compared to the scalar value directly.

So, you need to use the special function
JsonContains(mydata->'$[*].id', '62') = 1

3

Thank you so much! Now it works )