How can i import my JSON formatted data from my API to my docDB?

Hello everyone,

i want to create an iris document database with Atelier with some properties, where i can import my JSON formatted data from an API to the database which i created. Right now i know how to import my local JSON formatted data to my created database:

Class User.Classtest

{

ClassMethod getFile() as %Status

{

 

               set filename = "/home/student/Downloads/own_scrobble.json"

               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","$.name")

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

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

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

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

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

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

                              DO db.%CreateProperty("ArtistName","%Integer","$.artist/name")

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

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

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

                              set arr = ##class(%DynamicAbstractObject).%FromJSON(filename)

                              SET jstring = arr.%ToJSON()

                              DO db.%FromJSON(jstring)

                             

}

}

 

Now i want to take my JSON formatted data from my API (http://ws.audioscrobbler.com/2.0/?method=chart.gettopartists&api_key=652...) instead of my local json data(own_scrobble.json) but i don't know how to take the URL from my API as my datasource. Can you guys help me?

 

 

Best regards,

 

Duc Anh

 

  • 0
  • 0
  • 292
  • 7
  • 1

Answers

Sure thing...

Use the %Net.HttpRequest class, to make the HTTP request, and, take the response's data

Set httprequest=##class(%Net.HttpRequest).%New()
Set httprequest.Server="http://ws.audioscrobbler.com"
set URL="/2.0/?method=chart.gettopartists&api_key=65218c8cdd03ba3836f9fc8491fb6957&format=json&limit=1000&page=10"
Do httprequest.Get(URL)

The httprequest object has a property 'HttpResponse', now containing the http response.  The HttpResponse in turn, has a stream property 'Data' containing the entire http response body -

so, you can read off this Data stream to get your raw json and setup jstring variable, however, as I see you want to call %db.FromJSON, and, that method takes a stream object anyway - you can skip setting up the jstring variable and just do this directly which should work: 

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

 

Steve

Thank you very much Steve! I imported my JSON formatted API to my database and my code looks like this:

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)

            

}

}

 

But now i have the problem that my JSON formatted data from my API is imported only to the %Doc  column.  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. Is there a possibility to convert the  format from JSONObject to JSONArray?

 

Best regards,

 

Duc Anh

you can parse it, have a look on your Json 

Set result = httprequest.HttpResponse.Data.Read($$$MaxLength)
Do $System.OBJ.Dump(result)
 

Sorry i still don't know how to parse it. I only need the body of my JSON formatted data from my API. How can i only take the body of my API and import it to my docdb?

For example:

{
  "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"
          }
        ]

This is a part of my JSON formatted data from my API and i only need the "name", "playcount", "listeners" and so on as my headers without "artists" and  without "artist".  Do i need to parse it? Or is there another possibility how i can leave off the first two header?

Thank you in advance. 

Hi,

Sorry not getting back to you sooner - I was on a flight.

You are on the right track. You just need to understand the makeup  of the data returned.

So - to recap - in IRIS you are preparing a Document database - a collection of JSON documents. Each document represents an 'artist', with 'name', 'playcount', 'listeners' and other  properties.

The JSON string for 1 such entry (artist), or document in the collections would be look something like this, which is embedded actually, in the whole JSON your HTTP request returns..:

{
        "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"
          },...

}

If you extracted each artist document in the collection,  you can insert it into the database individually like this:

do db.%SaveDocument({"name":"Tough love","playcount":"279426",... })

The %SaveDocument method takes a single JSON dynamic object and inserts into the collection.  The whole JSON blob goes into the %DocDB 'column' of the projected table, and, for some elements like 'name' specifically created as columns via %CreateProperty - will be individually populated as column values.

But  - as mentioned earlier - the output from your HTTP call returns JSON which, down a few levels deep, has a collection of 'artist' documents :

{
  "artists": {
    "artist": [           <-- This is where the collection of artists starts from
      {
        "name": "Tough Love",
        "playcount": "279426",

Here are two approaches:

1. Option #1 - iterate through the correct part of your returned data, to extract individually each artist. (I prefer the next option #2)

Using the returned whole JSON, access the element 'artists', - and then, it's property 'artist'.  This element (represented by path "artists.artist", (poorly named imho), is actually the collection.  Use an iterator to iterate through each item in the collection.  

set wholeJSON={}.%FromJSON(httprequest.HttpResponse.Data)
set iArtist=wholeJSON.artists.artist.%GetIterator()  // iArtist is the iterator for the collection of artist JSON's
while iArtist.%GetNext(.key,.value) {
    // key is the item number in the collection
    // value is a dynamic object of the item in this collection
    do db.%SaveDocument(value)  //  insert 1 artist at a time.
}

2.  As you have discovered, you can use db.%FromJSON to import a whole collection of documents, in one hit, but, what you supply, should be a string or stream in JSON format, representing an array of documents, which the raw HttpResponse.Data is not because of the leading elements 'artists', etc..- but you can dive in and get the array:

set wholeJSON={}.%FromJSON(httprequest.HttpResponse.Data)
set arrArtists=wholeJSON.artists.artist   // this is the collection of artists
do db.%FromJSON(arrArtists.%ToJSON())  // need to give %FromJSON a json string.

.. and in one GULP, ALL artist documents, ie , all items in the collection, are added into the document database (I tried this - I confirm 1000 rows created).

Use option 1 if you want to filter the injection of data into your document database, or option 2 if you want to do a batch upload in one hit.

Let us know how you get on...

Steve


 

 

Thank you very much Steve! Everything works fine now.  smiley

I have a last question laugh Is it possible to import my collection row by row for like every 5 seconds? I want to create a streaming and always want to refresh my database. I think it's also okay if i can import my whole collection for like every hour. Thank you again in adance.

Don't see why not...

You've got to ask yourself  - do you want to hit that website (which returns the full set) every 5 seconds ?.. Probably not.  I would be hitting it every hour and, spend the time in between hits to go through and update the documents in the document database.

It's your choice whether to pause operations, delete all documents, and upload all documents every n seconds as a whole - that would be an easy approach. I think however, you can get clever and identify an element that can act as a key for you, and use it to extract individual documents and update them with changes, - then, insert new ones.   Keeping a track of rows inserted and updated with each cycle via some 'last updated' property, will also allow you to purge any rows which have been deleted and should no longer appear in your collection.

The above seems like a good approach, your use case may dictate a slightly different one. I'm not sure if there is a technical question here.  Technically - you will call the web site for the batch content in the same way, and, given the properties you already setup via CreateProperty - you can run an SQL Query to extract an individual document for updating/deleting.

Steve