XQuery How can I get the name of the Artist from the JSON

Question

I have the following JSON file

{
"href": "https://api.spotify.com/v1/playlists/37i9dQZF1DX2RxBh64BHjQ/tracks?offset=0&limit=100&additional_types=track",
"items": [
    {
        "added_at": "2021-11-29T23:29:54Z",
        "added_by": {
            "external_urls": {
                "spotify": "https://open.spotify.com/user/"
            },
            "href": "https://api.spotify.com/v1/users/",
            "id": "",
            "type": "user",
            "uri": "spotify:user:"
        },
        "is_local": false,
        "primary_color": null,
        "track": {
            "album": {
                "album_type": "single",
                "artists": [
                    {
                        "external_urls": {
                            "spotify": "https://open.spotify.com/artist/0Njy6yR9LykNKYg9yE23QN"
                        },
                        "href": "https://api.spotify.com/v1/artists/0Njy6yR9LykNKYg9yE23QN",
                        "id": "0Njy6yR9LykNKYg9yE23QN",
                        "name": "Nardo Wick",
                        "type": "artist",
                        "uri": "spotify:artist:0Njy6yR9LykNKYg9yE23QN"
                    }
                ],
                "available_markets": [
                    "AD",
                    "AE",
                    "AG",
                    "AL",
                    "AM",
                ],
                "external_urls": {
                    "spotify": "https://open.spotify.com/album/6SEeNB2xGW1kmysKSvWYqC"
                },
                "href": "https://api.spotify.com/v1/albums/6SEeNB2xGW1kmysKSvWYqC",
                "id": "6SEeNB2xGW1kmysKSvWYqC",
                ],
                "name": "Me or Sum (feat. Future & Lil Baby)",
            }

Im tying to get the name of the Artist, using Xquery, which is "Nardo Wick", but somethingis wrong. Here is my code:

  xquery version "3.1";
let $hou := fn:json-doc("spoti.json")?artists?*
return
   <Playlist>   
      <song>
      {
         for $item in fn:distinct-values($hou?name)
         return <name>{$item}</name>
      }
      </song>
   </Playlist>

So, my question is, how can I get the name of the artist from this JSON or anything else which is inside the "artists".

score 1 · Answer 1 · answered Dec 01 '21 at 23:07

1

You could consider using

let $hou := (fn:json-doc("spoti.json") => map:find("artists"))?*

Though this is a little fragile as it assumes the key "artists" doesn't appear anywhere else.

answered Dec 01 '21 at 23:07

Michael Kay

156,231
11
92
164

Dear Michael, Thank you for your answer, I made the following: `xquery version "3.1"; declare namespace map="http://www.w3.org/2005/xpath-functions/map"; let $hou := (fn:json-doc("spoti.json") => map:find("artists"))?* return { for $item in fn:distinct-values($hou?name) return {$item} } ` But I got the following error message: Engine name: Saxon-HE XQuery 9.8.0.12 Severity: error Description: First argument of '?' must be a sequence of maps – zedd Dec 02 '21 at 16:39
Update! It's working now: `let $hou := (fn:json-doc("spoti.json") => map:find("artists"))?* return { for $item in fn:distinct-values($hou?*?name) return {$item} } ` Thank you! – zedd Dec 02 '21 at 17:46

Martin Honnen · Accepted Answer · 2021-12-01T22:14:11.320

0

Your JSON snippet is not complete but some more realistic selection (attempting to select down into the presented JSON) would be ?items?1?track?album?artists?1?name. (Untested)

edited Dec 01 '21 at 22:14

answered Dec 01 '21 at 22:08

Martin Honnen

160,499
6
90
110

Dear Martin, It work fine like this: `Artist: {fn:count(fn:json-doc("spoti.json")?items?1?track?album?artists?1?*)}` The result is 6, which is correct because I have 6 Keys inside of the "Artists" . But when I try the following: `Artist: {fn:count(fn:json-doc("spoti.json")?items?1?track?album?artists?1?name?*)}` I get the following error message: Engine name: Saxon-HE XQuery 9.8.0.12 Severity: error Description: Required item type of value of variable $vv:LHS1687183879 is function(*); supplied value has item type xs:string Am I doing something wrong? – zedd Dec 02 '21 at 16:52
The `?items?1?track?album?artists?1?name` selection gives you a single string (e.g. `"Nardo Wick"`), it is not clear what you want to achieve by appending `?*` to it, that is a lookup you can do on a map or on an array but not on an atomic value like that string. – Martin Honnen Dec 02 '21 at 17:12
I guess there can be various artists involved and you want all names but you would do that using e.g. `?items?1?track?album?artists?*?name` instead of `?items?1?track?album?artists?1?name`. – Martin Honnen Dec 02 '21 at 17:16
You are correct, the JSON contains like 100 artists, and I want to get all the 100. I did the following and it works: `for $item in fn:distinct-values($hou?track?album?artists?*?name) return {$item}` Thank you so much for your help! – zedd Dec 02 '21 at 17:21
Fine, I guess instead of the `?items?1?track?album?artists?*?name` to select from the first item you wanted `?items?*?track?album?artists?*?name` as well or for the complete result in compact syntax `(?items?*?track?album?artists?*?name => distinct-values())!{.}`. But Mike's suggestion to use `map:find` is certainly a good way not to write too long lookup paths with JSON and XPath 3.1/XQuery 3.1. – Martin Honnen Dec 02 '21 at 17:30
Mike's version also working now: `let $hou := (fn:json-doc("spoti.json") => map:find("artists"))?* return { for $item in fn:distinct-values($hou?*?name) return {$item} } ` In the loop I forgot to write the "*": `($hou?*?name)` – zedd Dec 02 '21 at 17:43
Hey @MartinHonnen , can I ask a few more question? – zedd Dec 04 '21 at 16:51
Not as a comment, create a new question for each issue. – Martin Honnen Dec 04 '21 at 16:53

XQuery How can I get the name of the Artist from the JSON

2 Answers2