Getting Distinct values from JSON field

REST
1

Hi there everyone - I’m wondering if I’ve missed something in the docs. I have a json field, which contains multiple tags which can be present for more than one user.

User 1 - [‘Tag1’, ‘Tag2’, ‘Tag3’]
User 2 - [‘Tag3’]
User 3 - [‘Tag1’, ‘Tag3’]

As a specific example, I’d like to count the occurrences like so, splitting the JSON fields into their individual parts, getting the complete, unique list and counting the occurrences.

A GET request which would return something like this:

Tag: Tag1
Count: 2

Tag:Tag2
Count: 1

Tag: Tag3
Count: 3

I have no problem doing this with a normal text field with one value per row, but can’t see a way with a JSON field.

Is this possible? Or perhaps, is there a workaround that you’ve seen for this problem?

Thanks so much!
Rob

2

Hello @Rob_Simpson

Use JsonLength function
For example, I have jf json column, and to get count of array you should select property JsonLength(jf) as json_count:

Sergeys-MacBook-Pro:~ ksv$ curl -i "https://api.backendless.com/23298D6F-EA2F-7BE6-FFBF-7507875E1E00/1C2B5588-DDCA-418F-A202-185385180687/data/TestTableCreate?property=JsonLength(jf)%20as%20json_count&property=jf"
HTTP/1.1 200 OK

[
	{
		"___class": "TestTableCreate",
		"json_count": null,
		"jf": null
	},
	{
		"___class": "TestTableCreate",
		"json_count": 3,
		"jf": [
			"ddd",
			"dww",
			"eww"
		]
	},
	{
		"___class": "TestTableCreate",
		"json_count": 4,
		"jf": [
			"ddd",
			"dww",
			"eww",
			"www"
		]
	},
	{
		"___class": "TestTableCreate",
		"json_count": 2,
		"jf": [
			"ddd",
			"dww"
		]
	},
	{
		"___class": "TestTableCreate",
		"json_count": 2,
		"jf": [
			"ddd",
			"dww"
		]
	}
]
3

Hi Sergey,

Thanks very much for the suggestion - just tested that out and it looks like it counts the number of tags each row has in their json field.

What I’m actually looking to do is show all the unique tags, and then count the occurrences. The same tag can appear in multiple rows and I’m trying to count how often they appear.

Do you know if that is possible?
Appreciate the help!
Rob

4

I can advise you to try the following:

  1. consider the function JsonContains
    F.e. you have json document field meta_data which contains ["one", "two", "three"], then JsonContains(meta_data, '"two"') as jsonpart will return jsonpart: 1 and JsonContains(meta_data, '"five"') as jsonpart will return jsonpart: 0;

  2. consider the function Sum

As a result of combining of two functions: Sum(JsonContains(meta_data, '"two"')) will return the unique number of entries of "two" for every record in db row (only one for every json doc in case if it is present there).

5

Hi Oleg,

Thanks very much for the reply here - this solves part of what I’m trying to do. Particularly in the counting stage.

Where it doesn’t fully complete the task:
This is for a graph - I’m trying to make one request which brings back all unique tags with their count together as one response.

Also this requires me to know which tags are present first, so I’m trying to make a request which does something like, Split the JSON into tags, find all the unique entries and then count their occurrences.

Feels like a big ask for one request - is there any method in which I can do this as one process?

Thanks!
Rob

6

You are trying to use JSON as if it is a database. But it isn’t. It is a field with an extended capabilities. And the database know nothing about the content of the field until it reads the field.

So, if you have complex task for data manipulation, just use db itself, it is what it was designed for :slight_smile: