Read data from a JSON formatted column

Classic Servoy
1

Hi everyone,

I’m pretty new to Servoy. Nevertheless I’ve been asked to maintain a Servoy application - so I guess you’ll be seeing more of me here. :wink:
Anyway. Currently this application must be able to generate some report from the database, and I’ve come a long way in setting up the sql string and actually getting a set of records using databaseManager.getDataSetByQuery.
To my unpleasant surprise however,I discovered that certain relevant data cannot be retrieved directly from a column; this data is stored in JSON in this column - and my sql string needs to query a key-value pair within it.
Can this be done in Servoy - and if not, what would be a good workaround?

Thanks!

2

What database do you use?

3

You should check if the column convertor for this column is set to “StringSerializer”. In that case you can just get the values using the foundset:

var value1 = foundset.yourcolumnname.key1;

[attachment=0]stringserializer.png[/attachment]
If you want to use databaseManager.getDataSetByQuery() however, you can use JSON.parse() to get to the value you want:

	var dataSet = databaseManager.getDataSetByQuery(server_name,sql_query,arguments,max_returned_rows);
	var jsObject = JSON.parse(dataSet.getValue(1,1))
	var value1 = jsObject .key1;

stringserializer.png
stringserializer.png656×396 10.1 KB

4

rvanderburg:
What database do you use?

That’s SQL Server 2014.

5

Hi Joas,
Thank you for replying! I indeed want to use databaseManager.getDataSetByQuery, and now I manage to retrieve a single key/value pair quite nicely - e.g. “Intake” for “encounterName” in the below example. var value1 = jsObject[‘encounterName’] does the trick.
Unfortunately, what I need to have coughed up appears to be a few levels deeper, namely for every jsObject like this I need to have the “value” value if “key” equals to “DeliveryCaregivers.Gynaecologist”. See the below example: this should give me “1012”.

{  
   "actions":[  
      {  
         "class":"com.bma.common.dto.ActionDto",
         "entries":[  
            {  
               "class":"com.bma.common.dto.EntryDto",
               "id":null,
               "key":"DeliveryCaregivers.Gynaecologist",
               "performance":{  },
               "type":"Observation",
               "value":"1012"
            }
         ],
         "entryGroups":[  ],
         "id":null,
         "name":"DeliveryCaregivers",
         "performance":{  },
         "subject":{  },
         "type":"CareAction",
         "uuid":{  }
      }
   ],
   "class":"com.bma.emr.dto.EncounterDto",
   "dirty":true,
   "encounterName":"Intake",
   "numberOfChildren":3,
   "performance":{  },
   "type":"Encounter"
}

Ideally, I’d write something like

var query = "Select data.actions.entries.value ";
    query += "From myTable ";
    query += "Where data.actions.entries.key = 'DeliveryCaregivers.Gynaecologist'";

(the column name is “data”): in this way I’d have everything immediately incorporated in the sql string and that would save me a lot of time.
But I suppose something like this is not possible in Servoy (or is it actually?). However, where jsObject[‘encounterName’] works well, jsObject[“actions”[“entries”[“key”]] returns undefined - jsObject[“actions”[“class”"]] already returns undefined. jsObject[“actions”] returns [[object Object]].

Do you have a suggestion on how I can retrieve what I want?
Thanks once again!

6

If you want the database handle the JSON, SQL server has some functions for that, see for example: Work with JSON data in SQL Server - SQL Server | Microsoft Learn

I don’t have SQL server, so I can’t test that here.

In the JSON in your example, “actions” is an array of objects that have a “class”-property and an “entries”-property that is an array as well.
In your example those arrays contain just one item, if that is always the case, you can do it like this:

	var query = "Select data \
				From myTable \
				Where data LIKE ?"
	var dataSet = databaseManager.getDataSetByQuery(server_name, query, ["%DeliveryCaregivers.Gynaecologist%"], -1);
	var jsObject;
	for (var i = 1; i <= dataSet.getMaxRowIndex(); i++) {
		dataSet.rowIndex = i;
		jsObject = JSON.parse(dataSet.data);
		application.output(jsObject.actions[0].entries[0].value);
	}

If the arrays contain more items, you should loop through the arrays (instead of using [0]) to get the correct values.

7

Joas:
SQL server has some functions for that

Indeed, but only since SQL server 2017; I have SQL server 2014. And using these functions probably implies having to install SQL server 2017 at our customers as well. I’m not sure if that’s allowed.

But anyway,

application.output(jsObject.actions[0].entries[0].value);

makes sense. If I recall correctly, I got some error with this notation because .actions was not a member of jsObject or something… but perhaps that’s just me. I’ll be able to look into this next Thursday. It’s a pity that I’ll have to code around the SQL, but so be it. :)

Thanks for now - I’ll get back to you!

8

Hi Joas,

Your posts really helped me along; I was able to retrieve what I wanted with them.
I’ll suggest that our customers upgrade to SQL Server 2017 though (or 2016 actually) though, since taking advantage of its JSON handling features really makes life a lot easier. Still having to code around the dataset in order to tweak it, is A). a p. in the a., and B). shouldn’t be done anyway: that’s the sql job.

Thank you for your help!

9

You’re welcome. I’m glad to hear that you’ve got it working now.

I agree that in your case, where the selection depends on the data inside the JSON, it makes more sense to handle the JSON on the DB-side.
The fact that JSON is used in the first place seems like a strange choice though. Why not use an actual actions- and entries-table?

10

Joas:
Why not use an actual actions- and entries-table?

That’s beyond me. I didn’t design the thing. :?