New post
Question
· Oct 5, 2018

JSON array or JSON object?

#API #Databases #Data Import and Export #JSON

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. 

Discussion (6)0
Log in or sign up to continue
Duc Anh Tran · Oct 5, 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)

            

}

}

0 0
Stefan Cronje · Oct 6, 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)
0 0