Read data from a JSON formatted column

Questions and answers regarding general SQL and backend databases

Read data from a JSON formatted column

Postby c.groefsema » Fri Feb 22, 2019 1:19 pm

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!
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm

Re: Read data from a JSON formatted column

Postby rvanderburg » Fri Feb 22, 2019 4:57 pm

What database do you use?
rvanderburg
Site Admin
 
Posts: 69
Joined: Wed May 04, 2011 10:28 am

Re: Read data from a JSON formatted column

Postby Joas » Sun Feb 24, 2019 8:06 am

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:
Code: Select all
var value1 = foundset.yourcolumnname.key1;

stringserializer.png
stringserializer.png (15.97 KiB) Viewed 2273 times

If you want to use databaseManager.getDataSetByQuery() however, you can use JSON.parse() to get to the value you want:
Code: Select all
   var dataSet = databaseManager.getDataSetByQuery(server_name,sql_query,arguments,max_returned_rows);
   var jsObject = JSON.parse(dataSet.getValue(1,1))
   var value1 = jsObject .key1;
Joas de Haan
Yield Software Development: Need help on your project?
yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 834
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL

Re: Read data from a JSON formatted column

Postby c.groefsema » Mon Feb 25, 2019 12:10 pm

rvanderburg wrote:What database do you use?

That's SQL Server 2014.
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm

Re: Read data from a JSON formatted column

Postby c.groefsema » Mon Feb 25, 2019 5:35 pm

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".

Code: Select all

   "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
Code: Select all
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!
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm

Re: Read data from a JSON formatted column

Postby Joas » Mon Feb 25, 2019 9:00 pm

If you want the database handle the JSON, SQL server has some functions for that, see for example: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

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:
Code: Select all
   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.
Joas de Haan
Yield Software Development: Need help on your project?
yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 834
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL

Re: Read data from a JSON formatted column

Postby c.groefsema » Tue Feb 26, 2019 4:21 pm

Joas wrote: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!
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm

Re: Read data from a JSON formatted column

Postby c.groefsema » Thu Feb 28, 2019 2:04 pm

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!
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm

Re: Read data from a JSON formatted column

Postby Joas » Thu Feb 28, 2019 3:42 pm

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?
Joas de Haan
Yield Software Development: Need help on your project?
yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 834
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL

Re: Read data from a JSON formatted column

Postby c.groefsema » Thu Feb 28, 2019 4:20 pm

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

That's beyond me. I didn't design the thing. :?
User avatar
c.groefsema
 
Posts: 26
Joined: Fri Feb 22, 2019 12:40 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 2 guests