It is currently October 22nd, 2019, 6:01 pm

JSON to XML with Yahoo! Query Language

Tips and Tricks from the Rainmeter Community
User avatar
moshi
Posts: 1851
Joined: November 13th, 2012, 9:53 pm

JSON to XML with Yahoo! Query Language

moshi » December 16th, 2013, 1:00 pm

some websites offer their content only as JSON feed. an example is Dribbble. http://dribbble.com/

the feed for their currently most popular posts is this: http://api.dribbble.com/shots/

Code: Select all

{"page":"1","per_page":15,"pages":50,"total":750,"shots":[{"id":1346827,"title":"The First Timer","height":600,"width":800,"likes_count":199,"comments_count":9,"rebounds_count":0,"url":"http://dribbble.com/shots/1346827-The-First-Timer","short_url":"http://drbl.in/jEeB","views_count":2630,"rebound_source_id":null,"image_url":"http://d13yacurqjgara.cloudfront.net/users/255/screenshots/1346827/first_timer_r3.png","image_teaser_url":"http://d13yacurqjgara.cloudfront.net/users/255/screenshots/1346827/first_timer_r3_teaser.png","image_400_url":"http://d13yacurqjgara.cloudfront.net/users/255/screenshots/1346827/first_timer_r3_1x.png","player":{"id":255,"name":"Rogie King","location":"Helena, Montana","followers_count":23041,"draftees_count":31,"likes_count":9888,"likes_received_count":69504,"comments_count":5472,"comments_received_count":9410,"rebounds_count":74,"rebounds_received_count":403,"url":"http://dribbble.com/rogie","avatar_url":"http://d13yacurqjgara.cloudfront.net/users/255/avatars/normal/rogie.png?1373600206","username":"rogie","twitter_screen_name":"rogie","website_url":"http://rog.ie","drafted_by_player_id":171,"shots_count":269,"following_count":1451,"created_at":"2009/12/15 19:13:41 -0500"},"created_at":"2013/12/14 10:28:04 -0500"},{"id":1347327,"title":"Coffee search","height":600,"width":800,"likes_count":202,"comments_count":12,"rebounds_count":0,"url":"http://dribbble.com/shots/1347327-Coffee-search","short_url":"http://drbl.in/jEoh","views_count":2275,"rebound_source_id":null,"image_url":"http://d13yacurqjgara.cloudfront.net/users/30593/screenshots/1347327/banner.jpg","image_teaser_url":"http://d13yacurqjgara.cloudfront.net/users/30593/screenshots/1347327/banner_teaser.jpg","image_400_url":"http://d13yacurqjgara.cloudfront.net/users/30593/screenshots/1347327/banner_1x.jpg","player":{"id":30593,"name":"Alex Volkov","location":"Ukraine","followers_count":6723,"draftees_count":3,"likes_count":1071,"likes_received_count":30380,"comments_count":257,"comments_received_count":1926,"rebounds_count":0,"rebounds_received_count":1,"url":"http://dribbble.com/kadasarva","avatar_url":"http://d13yacurqjgara.cloudfro....
that's a JSON feed. while it is sure possible to parse this with the regular expressions of the webparser plugin, i really hate to do that.


YQL makes that a lot easier. let's go to the YQL Console: http://developer.yahoo.com/yql/console/
and lets enter this query:

Code: Select all

select * from json where url="http://api.dribbble.com/shots/"
the result is an XML feed:

Code: Select all

<results>
        <json>
            <page>1</page>
            <per_page>15</per_page>
            <pages>50</pages>
            <total>750</total>
            <shots>
                <id>1346827</id>
                <title>The First Timer</title>
                <height>600</height>
                <width>800</width>
                <likes_count>200</likes_count>
                <comments_count>9</comments_count>
                <rebounds_count>0</rebounds_count>
                <url>http://dribbble.com/shots/1346827-The-First-Timer</url>
                <short_url>http://drbl.in/jEeB</short_url>
                <views_count>2639</views_count>
                <rebound_source_id>null</rebound_source_id>
                <image_url>http://d13yacurqjgara.cloudfront.net/users/255/screenshots/1346827/first_timer_r3.png</image_url>
                <image_teaser_url>http://d13yacurqjgara.cloudfront.net/users/255/screenshots/1346827/first_timer_r3_teaser.png</image_teaser_url>
                <image_400_url>http://d13yacurqjgara.cloudfront.net/users/255/screenshots/1346827/first_timer_r3_1x.png</image_400_url>
                <player>
                    <id>255</id>
                    <name>Rogie King</name>
                    <location>Helena, Montana</location>
                    <followers_count>23041</followers_count>
                    <draftees_count>31</draftees_count>
                    <likes_count>9888</likes_count>
                    <likes_received_count>69505</likes_received_count>
                    <comments_count>5472</comments_count>
                    <comments_received_count>9410</comments_received_count>
                    <rebounds_count>74</rebounds_count>
                    <rebounds_received_count>403</rebounds_received_count>
                    <url>http://dribbble.com/rogie</url>
                    <avatar_url>http://d13yacurqjgara.cloudfront.net/users/255/avatars/normal/rogie.png?1373600206</avatar_url>
                    <username>rogie</username>
                    <twitter_screen_name>rogie</twitter_screen_name>
                    <website_url>http://rog.ie</website_url>
                    <drafted_by_player_id>171</drafted_by_player_id>
                    <shots_count>269</shots_count>
                    <following_count>1451</following_count>
                    <created_at>2009/12/15 19:13:41 -0500</created_at>
                </player>
                <created_at>2013/12/14 10:28:04 -0500</created_at>
            </shots>
            <shots>
                <id>1346874</id>
                <title>Weather Dashboard / Global Outlook (5)</title>
                <height>600</height>
                <width>800</width>
                <likes_count>221</likes_count>
                <comments_count>14</comments_count>
                <rebounds_count>0</rebounds_count>
                <url>http://dribbble.com/shots/1346874-Weather-Dashboard-Global-Outlook-5</url>
                <short_url>http://drbl.in/jEfw</short_url>
                <views_count>5018</views_count>
                <rebound_source_id>1342715</rebound_source_id>....
much nicer. :)
User avatar
moshi
Posts: 1851
Joined: November 13th, 2012, 9:53 pm

Re: JSON to XML with Yahoo! Query Language

moshi » December 16th, 2013, 1:11 pm

it doesn't stop here. using select, you can reduce the feed to only the information you need. let's say we only want the preview image, the title and a link:

Code: Select all

select shots.title,shots.url,shots.image_teaser_url from json where url="http://api.dribbble.com/shots/" 
the result is even nicer than before:

Code: Select all

 <results>
        <json>
            <shots>
                <title>The First Timer</title>
                <url>http://dribbble.com/shots/1346827-The-First-Timer</url>
    <image_teaser_url>http://d13yacurqjgara.cloudfront.net/users/255/screenshots/1346827/first_timer_r3_teaser.png</image_teaser_url>
            </shots>
        </json>
        <json>
            <shots>
                <title>Coffee search</title>
                <url>http://dribbble.com/shots/1347327-Coffee-search</url>
                <image_teaser_url>http://d13yacurqjgara.cloudfront.net/users/30593/screenshots/1347327/banner_teaser.jpg</image_teaser_url>
            </shots>
        </json>
        <json>
            <shots>
                <title>Weather Dashboar
...
YQL has more features, one of the most interesting is sort. let's sort these alphabetically:

Code: Select all

select shots.title,shots.url,shots.image_teaser_url from json where url="http://api.dribbble.com/shots/" | sort(field="shots.title")

Code: Select all

<results>
        <json>
            <shots>
                <title>Bizaar Sketch</title>
                <url>http://dribbble.com/shots/1347304-Bizaar-Sketch</url>
                <image_teaser_url>http://d13yacurqjgara.cloudfront.net/users/193534/screenshots/1347304/_________-1_teaser.jpg</image_teaser_url>
            </shots>
        </json>
        <json>
            <shots>
                <title>Coffee search</title>
                <url>http://dribbble.com/shots/1347327-Coffee-search</url>
                <image_teaser_url>http://d13yacurqjgara.cloudfront.net/users/30593/screenshots/1347327/banner_teaser.jpg</image_teaser_url>
            </shots>
        </json>
        <json>
            <shots>
                <title>Dashboard Redesign</title>
                <url>http://dribbble.com/shots/1348013-Dashboard-Redesign</url>
                <image_teaser_url>http://d13yacurqjgara.cloudfront.net/users/756/screenshots/1348013/shot_teaser.png</image_teaser_url>
            </shots>
...
and once you're done, look at the bottom of the YQL Console website and copy the encoded url and use it with the webparser plugin in Rainmeter:

Code: Select all

Url=http://query.yahooapis.com/v1/public/yql?q=select%20shots.title%2Cshots.url%2Cshots.image_teaser_url%20from%20json%20where%20url%3D%22http%3A%2F%2Fapi.dribbble.com%2Fshots%2F%22%20%7C%20sort(field%3D%22shots.title%22)&diagnostics=true
Last edited by moshi on December 16th, 2013, 1:16 pm, edited 1 time in total.
User avatar
moshi
Posts: 1851
Joined: November 13th, 2012, 9:53 pm

Re: JSON to XML with Yahoo! Query Language

moshi » December 16th, 2013, 1:14 pm

the Dribbble JSON feed isn't even that bad, but there are worse with more hierarchies in the tree. YQL can really make your life easier if you encounter such a feed.