Question
Duc Anh Tran · Oct 5, 2018

JSON array or JSON object?

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. 

#API #Databases #Data Import and Export #JSON #Atelier
00
3 0 6 1,276
Log in or sign up to continue

Replies

Stefan Cronje · Oct 5, 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?

00
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)

            

}

}

00
Neerav Adam Verma · Jul 17, 2019

Link you have given and the json you have pasted are totally different

If you can put the right json or point to the right rest, should be able to help you out

00
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)
00
oklp pl · Feb 19, 2021

Sorry, I don't quite understand what you mean. And your json also has format problems, you can use this to verify 

JSON Validator

00