JSON array or JSON object?

API, Databases, Atelier, JSON, InterSystems IRIS

Hello everyone,

i already asked this question in another post (https://community.intersystems.com/post/how-can-i-import-my-json-formatt...) and i'm sorry for creating a new post but i still didn't get an answer so i try it again. Now i know how to import my JSON formatted data from my API to my DocDB but now i have the problem that my JSON formatted data from my API is imported only to the %Doc column and not to the columns that i created with my properties. I'm not really sure but i think that the document database only puts my information from my API to the right columns if my JSON is in a JSONArrray format. But the format of my API is JSONObject and looks like this:

{
"artists": {
"artist": [
{
"name": "Tough Love",
"playcount": "279426",
"listeners": "58179",
"mbid": "d07276bc-3874-4deb-8699-35c9948be0cc",
"url": "https://www.last.fm/music/Tough+Love",
"streamable": "0",
"image": [
{
"#text": "https://lastfm-img2.akamaized.net/i/u/34s/3fa24f60a855fdade245138dead7ec...",
"size": "small"
},
{
"#text": "https://lastfm-img2.akamaized.net/i/u/64s/3fa24f60a855fdade245138dead7ec...",
"size": "medium"
},
{
"#text": "https://lastfm-img2.akamaized.net/i/u/174s/3fa24f60a855fdade245138dead7e...",
"size": "large"
},
{
"#text": "https://lastfm-img2.akamaized.net/i/u/300x300/3fa24f60a855fdade245138dea...",
"size": "extralarge"
},
{
"#text": "https://lastfm-img2.akamaized.net/i/u/300x300/3fa24f60a855fdade245138dea...",
"size": "mega"
}
]

Is there a possibility to convert the format from JSONObject to JSONArray or do i have to parse it? I want to import only the body from my API with "name", "playcount", "listeners" and so on as my headers without "artists" and without "artist". I just want to leave off the first two headers.

Thank you in advance.

Answers

Answer by Stefan Cronje 6 October 2018

This is just an idea, and I may be corrected on this.
You get an array of artists. I can't find anything on storing arrays of the same key as you have in this scenario.

I would first do the web-service call.

Then create a DynamicObject using %FromJSON and the http response data as the source.
Get the array of artist from the DynamicObject using %Get("artists") on the DynamicObject instance.
Create an iterator on the object returned by the previous command using %GetIterator
Iterate through the collection - for each
- Create a docdb entry and the properties or open an existing one. I recommend creating a separate method for this to keep the code concise.
- Create a JSON stream of the current object on the iterator using %ToJSON(.tMyStream)
- DO db.%FromJSON(.tMyStream)

Answer by james malvi 11 October 2018

You can start playing with JSON using these tools. https://jsonformatter.org and https://codebeautify.org/jsonviewer

Comments

Comment by Stefan Cronje 5 October 2018

Which properties of the JSON are you trying to store? Is it one of the properties from the "artist" object in the "artists" array?

Can you provide the document class definition?

Comment by Duc Anh Tran 5 October 2018

Im trying to store the properties "name", "playcount", "listeners", "mbid", "url" not the "artist" or the "artists" property. I think that my document class definition is at the bottom of my code :

Class User.API

{

ClassMethod getFile() as %Status

{

IF $SYSTEM.DocDB.Exists("db.Streamingdatabase") {

SET db = ##class(%DocDB.Database).%GetDatabase("db.Streamingdatabase")

}

ELSE {

SET db = ##class(%DocDB.Database).%CreateDatabase("db.Streamingdatabase")

}

DO db.%CreateProperty("Name","%String(MAXLEN=200)","$.name")

DO db.%CreateProperty("Duration","%Integer","$.duration")

DO db.%CreateProperty("Playcount","%Integer","$.playcount")

DO db.%CreateProperty("Mbid","%String(MAXLEN=200)","$.mbid")

DO db.%CreateProperty("Url","%String(MAXLEN=200)","$.url")

DO db.%CreateProperty("StreamableText","%Integer","$.streamable/#text")

DO db.%CreateProperty("StreamableFulltrack","%Integer","$.streamable/fulltrack")

DO db.%CreateProperty("ArtistName","%String(MAXLEN=200)","$.artist/name")

DO db.%CreateProperty("ArtistMbid","%String(MAXLEN=200)","$.artist/mbid")

DO db.%CreateProperty("ArtistURL","%String(MAXLEN=200)","$.artist/url")

DO db.%CreateProperty("Rank","%Integer","$.@attr/rank")

SET httprequest=##class(%Net.HttpRequest).%New()

DO httprequest.Get("http://ws.audioscrobbler.com/2.0/?method=user.gettoptracks&user=ducanhtr...")

DO db.%FromJSON(httprequest.HttpResponse.Data)

}