Problem with my JQ

[marshru]

Continuing the discussion from Transform JSON with JQ:

I tried this same approach but it returns nothing. I ran the JQ in an online parser to make sure it worked first but it isn’t working the same in Glide. The Json is very short and properly formatted but I’m getting weird errors (seems to me it’s not making an assignment to “j”?):

P2 is a Number column from 0 to 9.
P1 is a lookup from another table containing the JSON:
{
“page”:1,
“results”:[
{
“adult”:false,
“backdrop_path”:“/wvdiTDDBxI0lix4Wbc2qePfgkA2.jpg”,
“genre_ids”:[
35,
80,
9648
],
“id”:107113,
“origin_country”:[
“US”
],
“original_language”:“en”,
“original_name”:“Only Murders in the Building”,
“overview”:“Three strangers who share an obsession with true crime suddenly find themselves wrapped up in one.”,
“popularity”:41.885,
“poster_path”:“/ArsVbYhS6DiFJtRAlV8ytAauNlp.jpg”,
“first_air_date”:“2021-08-31”,
“name”:“Only Murders in the Building”,
“vote_average”:8.5,
“vote_count”:935
},
{
“adult”:false,
“backdrop_path”:“/m6C5RVGEHL8U08WIcgFh0xqUtON.jpg”,
“genre_ids”:[
10767
],
“id”:210426,
“origin_country”:[
“US”
],
“original_language”:“en”,
“original_name”:“One Killer Question”,
“overview”:“The Only Murders in the Building aftershow that isn’t afraid to tackle the hard question. One question. That’s it. Featuring host Ali Stroker, the show’s actors, a slew of celebrity superfans and one big murder to solve it just might be the only Only Murders In the Building aftershow you need to watch.”,
“popularity”:1.585,
“poster_path”:null,
“first_air_date”:“2022-06-28”,
“name”:“One Killer Question”,
“vote_average”:2,
“vote_count”:1
}
],
“total_pages”:1,
“total_results”:2
}

[Uzo]

you need to fix your JSON to an array []

[marshru]

Ok, thanks, tried that, but still getting errors. I presume I have to index into the array now that I’ve done that, so changed the code slightly, and now get this:

(Note: I had a senior moment and hadn’t referenced the Index field before (not that this was the issue). I fixed that before trying again - per image above. )

If I don’t do that first index (say “JSON.parse” takes care of that), I get another error:

Which tells me I might be making progress but why didn’t it fail out of the if statement after the second Index rather than throw an error?

Sorry, I’m very new to JQuery and Javascript.

[Uzo]

Your payload is just the headers, so you need to address them by their name, not by positions…
return j.page
it should give you “1”

[Uzo]

give me the fetch url, and what you wanna get from payload, and I will fix the code for you

[marshru]

Ok I kind of see, but this doesn’t work either:

[marshru]

Um, the URL has my API key so can’t send here but, basically, see below. As far as what I wanted to get - I was just experimenting, and to start with just want to get the two hits in a separate row.

So I can enter any series name and get back all the hits from their site. The example result is for “only murders in the building” : the result comes back as show in the original message which I subsequently wrapped in an array format [ … ] per discussion. See below for the actual JSON string returned:

{
“page”:1,
“results”:[
{
“adult”:false,
“backdrop_path”:“/wvdiTDDBxI0lix4Wbc2qePfgkA2.jpg”,
“genre_ids”:[
35,
80,
9648
],
“id”:107113,
“origin_country”:[
“US”
],
“original_language”:“en”,
“original_name”:“Only Murders in the Building”,
“overview”:“Three strangers who share an obsession with true crime suddenly find themselves wrapped up in one.”,
“popularity”:41.885,
“poster_path”:“/ArsVbYhS6DiFJtRAlV8ytAauNlp.jpg”,
“first_air_date”:“2021-08-31”,
“name”:“Only Murders in the Building”,
“vote_average”:8.5,
“vote_count”:935
},
{
“adult”:false,
“backdrop_path”:“/m6C5RVGEHL8U08WIcgFh0xqUtON.jpg”,
“genre_ids”:[
10767
],
“id”:210426,
“origin_country”:[
“US”
],
“original_language”:“en”,
“original_name”:“One Killer Question”,
“overview”:“The Only Murders in the Building aftershow that isn’t afraid to tackle the hard question. One question. That’s it. Featuring host Ali Stroker, the show’s actors, a slew of celebrity superfans and one big murder to solve it just might be the only Only Murders In the Building aftershow you need to watch.”,
“popularity”:1.585,
“poster_path”:null,
“first_air_date”:“2022-06-28”,
“name”:“One Killer Question”,
“vote_average”:2,
“vote_count”:1
}
],
“total_pages”:1,
“total_results”:2
}

[Darren_Murphy]

You are trying to address an array that does not exist.
The following works:

let json = JSON.parse(p1);
return JSON.stringify(json.results);

What are you actually trying to extract from that JSON?

[Robert_Petitto]

Right…just extract the name of each show for example?

[marshru]

Ok - I think I am misunderstanding the way the thing is called. What is in P1? I have a column with the JSON results in P1 and P2 is the index. Is that correct? Also, should this be an array or a string?

[Darren_Murphy]

The JSON is passed into the JavaScript column as a string, and then the JSON.parse() method is used to convert that string to a JSON object.

You then directly access various attributes of the object.
The index is useful if your JSON contains a collection (array) and you want to retrieve a specific element of the array.

Again, which specific items do you want to extract from your sample JSON?

If you share that, I’ll show you how to do it.

[marshru]

Yep, Ok this is what I thought. In the above I am trying to get each of the two “results” objects ( “results”:[{…},{…}]) - each in a new row. Happy to get anything from there at this stage, say "name’ or “overview” if that is easier. I just need to see some syntax that works. Nothing I’ve tried so far has had a result. I’ve attempted all the above suggestions with variations. I’m sure there’s something really obvious I’m getting wrong but can’t figure it out.

By the way - it looks like this hard coded in JQuery:

[Darren_Murphy]

Okay, given the following JSON string:

 {
   "page":1,
   "results":[
      {
         "adult":false,
         "backdrop_path":"/wvdiTDDBxI0lix4Wbc2qePfgkA2.jpg",
         "genre_ids":[
            35,
            80,
            9648
         ],
         "id":107113,
         "origin_country":[
            "US"
         ],
         "original_language":"en",
         "original_name":"Only Murders in the Building",
         "overview":"Three strangers who share an obsession with true crime suddenly find themselves wrapped up in one.",
         "popularity":41.885,
         "poster_path":"/ArsVbYhS6DiFJtRAlV8ytAauNlp.jpg",
         "first_air_date":"2021-08-31",
         "name":"Only Murders in the Building",
         "vote_average":8.5,
         "vote_count":935
      },
      {
         "adult":false,
         "backdrop_path":"/m6C5RVGEHL8U08WIcgFh0xqUtON.jpg",
         "genre_ids":[
            10767
         ],
         "id":210426,
         "origin_country":[
            "US"
         ],
         "original_language":"en",
         "original_name":"One Killer Question",
         "overview":"The Only Murders in the Building aftershow that isn't afraid to tackle the hard question. One question. That's it. Featuring host Ali Stroker, the show's actors, a slew of celebrity superfans and one big murder to solve it just might be the only Only Murders In the Building aftershow you need to watch.",
         "popularity":1.585,
         "poster_path":null,
         "first_air_date":"2022-06-28",
         "name":"One Killer Question",
         "vote_average":2,
         "vote_count":1
      }
   ],
   "total_pages":1,
   "total_results":2
}

To extract the movie names, each on a separate row, use the following:

let json = JSON.parse(p1);
return json.results[p2].original_name;

PS. (Which is basically the same as the example that Bob gave you earlier)

[marshru]

Yep - all the advice was correct I was not using the right syntax to stringify the whole object when trying that. It now works. Very pleased I’m half way round the world and not likely to bump into any of you. Many thanks all for your patience, and sorry to give you such a run around.

[Darren_Murphy]

No problems. We’re here to share and help. We’ve all been where you are at one point.

[Uzo]

Sorry, I had a customer meeting… but I see Darren and Robert took care of you

[Yohannan]

@Darren_Murphy
I’m new too & i’m stuck with array data
I am trying to take present data of every dates & want to seperate them by dates

I actually want to show attendance report of present & absent with names in collection of different dates

This is my JSON of 1 subject

{
“subjects”: [
{
“subject_name”: “JJ12345”,
“attendance”: [
{
“date”: “2023-04-11T18:30:00.000Z”,
“attendance_data”: {
“present”: [
“ID001”,
“ID003”
],
“absent”: [
“ID002”
]
}
},
{
“date”: “2023-04-12T18:30:00.000Z”,
“attendance_data”: {
“present”: [
“ID001”,
“ID002”,
“ID003”
],
“absent”: [
“”
]
}
},
{
“date”: “2023-04-13T18:30:00.000Z”,
“attendance_data”: {
“present”: [
“ID001”,
“ID002”,
“ID003”
],
“absent”: [
“”
]
}
}
]
}
]
}

[Darren_Murphy]

Given your JSON structure:

• Create a working table and number the rows starting at zero
• Add a column that contains the JSON that you want to process
• Use JavaScript to extract the values that you need
• In each of the examples below, p1 is your JSON string, and p2 is the Row Index.

To extract the Subject Name:

let json = JSON.parse(p1);
return json.subjects[0].subject_name;

To extract the Date:

let json = JSON.parse(p1);
return json.subjects[0].attendance[p2].date;

To extract a list of those Present:

let json = JSON.parse(p1);
let present = json.subjects[0].attendance[p2].attendance_data.present;
return present.join(",");

To extract a list of those Absent:

let json = JSON.parse(p1);
let absent = json.subjects[0].attendance[p2].attendance_data.absent;
return absent.join(",");

End result:

You may need to tweak the above if your JSON contains data for multiple Subjects, but this should be enough to get you started.