Take only first value from list of returned values #67

ronlut · 2018-01-30T15:40:18Z

Hi.
I am wondering whether it's somehow possible to take only first value (or value at location i) when receiving a list of values in json.
For example, for this output:

"data": [
    {
        "price": 1
    },
    {
        "price": 2
    },
]

I would like to get 1 using something like /data/price/[0].
Is this something currently possible?
Thanks

The text was updated successfully, but these errors were encountered:

tordans · 2018-01-30T16:22:33Z

@ronlut maybe check out https://gist.github.com/chrislkeller/5719258#gistcomment-2272498 => https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4 which looks like it allows you to just select specific array items.
This forks might be outdated though since it is based on the gist-version of this script which - from my understanding - came before the github repo.

ronlut · 2018-01-31T11:58:37Z

@tordans Thanks. I'll take a look at it and create a pull request to merge it with this repo :)

Hobbesball · 2018-02-15T12:57:19Z

@tordans @ronlut did you get this to work, in the end? I'm trying to query my JSON results in the same way, but still get 'An array value cannot be found' errors.

e.g. my working call is ImportJSON("http://europeana.eu/api/v2/search.json?query="books"&rows=0&start=1&profile=facets&wskey=api2demo", "/facets/fields/label", "noTruncate,noHeaders")
I want to be able to get different rows in the 'fields' array, like so: ImportJSON("http://europeana.eu/api/v2/search.json?query="books"&rows=0&start=1&profile=facets&wskey=api2demo", "/facets/fields[2]/label", "noTruncate,noHeaders"), which should work with @allenyllee's script but doesn't.

allenyllee · 2018-02-15T14:09:17Z

Hi @Calvinwuyts ,

It seems that the parameter in your function call may have two problems:

The URL "http://europeana.eu/api/v2/search.json?query="books"&rows=0&start=1&profile=facets&wskey=api2demo" seems broken, because the double quote of "books". It'll cause parsing error. Removing the double quote fix this.
The XPath "/facets/fields[2]/label" is incorrect, because "/facets" also need array index. Using "/facets[0]/fields[2]/label" it'll give you label in /facets[0]/fields[2], and similarly, "/facets[1]/fields[2]/label", "/facets[3]/fields[2]/label"... etc.

Below is the fixed code:

ImportJSON("http://europeana.eu/api/v2/search.json?query=books&rows=0&start=1&profile=facets&wskey=api2demo", "/facets[0]/fields[2]/label", "noTruncate,noHeaders")

Hobbesball · 2018-02-15T15:06:16Z

hi @allenyllee , thank you so much, that makes a bunch of sense! I got it to work perfectly. Thanks again!

ronlut · 2018-02-15T15:26:13Z

@Calvinwuyts Sorry for joining late, you can take a look at @allenyllee's solution integrated into this library by looking at #68 :)
(forgot to update until I saw the message from you)

Hobbesball · 2018-02-15T16:20:41Z

hiya @ronlut, thanks for the pull!

mtompkins · 2018-03-12T21:59:22Z

I believe I'm seeing a sort of similar effect where the api call is returning all results that are equal or a subset of the query which is causing a little difficulty.

Example: http://whattomine.com/coins/214.json returns

{  
   "id":214,
   "name":"BitcoinGold",
   "tag":"BTG",
   "algorithm":"Equihash",
   "block_time":"550.0",
   "block_reward":12.5,
   "block_reward24":12.5,
   "block_reward3":12.5,
   "block_reward7":12.5,
   "last_block":518149,
   "difficulty":3610556.00068795,
   "difficulty24":3143037.1281391,
   "difficulty3":3323764.18655435,
   "difficulty7":3426535.49293562,
   "nethash":53777590,
   "exchange_rate":0.008639,
   "exchange_rate24":0.00864725263157894,
   "exchange_rate3":0.00880546407163743,
   "exchange_rate7":0.00897479941744241,
   "exchange_rate_vol":98.55452268,
   "exchange_rate_curr":"BTC",
   "market_cap":"$1,324,576,111",
   "pool_fee":"0.000000",
   "estimated_rewards":"0.036492",
   "btc_revenue":"0.00031525",
   "revenue":"$2.85",
   "cost":"$0.86",
   "profit":"$1.98",
   "status":"Active",
   "lagging":true,
   "timestamp":1520887718
}

If I try to extract the value at difficulty, the return includes all 4 difficulty* results:

=ImportJSON("http://whattomine.com/coins/214.json", "/difficulty")

returns:

	24	3	7
3610556.00068795	3143037.1281391	3323764.18655435	3426535.49293562

Is it possible to limit the return to the explicit item?

allenyllee · 2018-03-13T04:31:31Z

Hi @mtompkins ,
My gist version is OK. It seems that the PR #68 by @ronlut has not yet been merged.

mtompkins · 2018-03-13T09:30:34Z

@allenyllee Thank you, however, I do not see this resolving the issue. Perhaps I misunderstand its usage. If I try =ImportJSON("http://whattomine.com/coins/214.json", "/difficulty[0]") it fails.

allenyllee · 2018-03-13T12:38:37Z

@mtompkins Try ImportJSON("http://whattomine.com/coins/214.json", "/difficulty")

mtompkins · 2018-03-13T13:57:48Z

Your GIST has the same effect - all 4 are returned.

allenyllee · 2018-03-13T16:23:14Z

@mtompkins No, just one!

mtompkins · 2018-03-13T16:28:13Z

Using https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4
I get 4 responses

allenyllee · 2018-03-14T03:26:05Z

Hi @mtompkins, sorry, I can not reproduce this issue....

mtompkins · 2018-03-14T20:40:37Z

So it's clearly a Google Sheets issue. I created a totally new document, pasted your GIST and it works as you have described. The old sheet doesn't seem to update the function / script correctly. What a pain!

loschguy · 2018-05-04T21:02:30Z

Try to get Google map API to sort a list of Costco locations. Thanks

Anyway to get "San Bernardino County" for "types" : [ "administrative_area_level_2", "political" ].
please note that some result set might not contain "types" : [ "administrative_area_level_2", "political" ] if data not available.

{
"results" : [
{
"address_components" : [
{
"long_name" : "13111",
"short_name" : "13111",
"types" : [ "street_number" ]
},
{
"long_name" : "Peyton Drive",
"short_name" : "Peyton Dr",
"types" : [ "route" ]
},
{
"long_name" : "Chino Hills",
"short_name" : "Chino Hills",
"types" : [ "locality", "political" ]
},
{
"long_name" : "San Bernardino County",
"short_name" : "San Bernardino County",
"types" : [ "administrative_area_level_2", "political" ]
},
{
"long_name" : "California",
"short_name" : "CA",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "United States",
"short_name" : "US",
"types" : [ "country", "political" ]
},
{
"long_name" : "91709",
"short_name" : "91709",
"types" : [ "postal_code" ]
},
{
"long_name" : "6002",
"short_name" : "6002",
"types" : [ "postal_code_suffix" ]
}
],
"formatted_address" : "13111 Peyton Dr, Chino Hills, CA 91709, USA",
"geometry" : {
"location" : {
"lat" : 34.0141463,
"lng" : -117.7424536
},
"location_type" : "ROOFTOP",
"viewport" : {
"northeast" : {
"lat" : 34.0154952802915,
"lng" : -117.7411046197085
},
"southwest" : {
"lat" : 34.0127973197085,
"lng" : -117.7438025802915
}
}
},
"partial_match" : true,
"place_id" : "ChIJLYJzn2Mtw4ARDSJACuj_XMA",
"types" : [ "street_address" ]
}
],
"status" : "OK"
}

NickB23 · 2018-07-14T15:26:00Z

@allenyllee I'm using your gist https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4

If I am using your example:
ImportJSON("http://europeana.eu/api/v2/search.json?query=books&rows=0&start=1&profile=facets&wskey=api2demo", "/facets[0]/fields[2]/label", "noHeaders, noTruncate")

My result is Biblionet no matter what I try (tried several accounts, opening different spreadsheets):

while the result should have been:
"Bibliothèque nationale de France"

However when I change it to:
ImportJSON("http://europeana.eu/api/v2/search.json?query=books&rows=0&start=1&profile=facets&wskey=api2demo", "/facets[2]/fields[1]/label", "noTruncate,noHeaders")

I do get the correct answer (1688)!

There seems to be a problem when the first argument is [0].

I'm asking because I want to use the Google Maps API. Whenever I use:
=ImportJSON("https://maps.googleapis.com/maps/api/geocode/json?address=New+York&key={API_KEY_HERE}", "/results[0]/address_components[2]/long_name", "noHeaders, noTruncate")

The result is:

While it should have been "United States":

What am I doing wrong?

allenyllee · 2018-07-16T13:53:57Z

@NickB23 Thanks for your report. You are right, this is an issue. Here is my fixed code https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4

// Changelog:
// (Jul. 16 2018) tag: allenyllee-20180716
// 1. Fixed the issue "If you try to query /arrayA[k]/arrayB[n]/arrayC[m]/.../member, you will always get /arrayA[k]/arrayB[k]/arrayC[k]/.../member."

Below is the test example which query from /facets[0]/fields[0]/label to /facets[10]/fields[10]/label

NickB23 · 2018-07-16T14:32:15Z

@allenyllee It's working now! Thanks!

gitboss · 2018-08-07T14:31:14Z

@allenyllee I'm using your gist - https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4

While using the following example -

=ImportJSON("https://www.reddit.com/r/news/comments/94ssi3/80yearold_medical_marijuana_patient_with_expired/.json?limit=1","/data[0]/children[0]/subreddit","noHeaders")

I expect to get and I get the correct result that is = news

However if I use the following

=ImportJSON("https://www.reddit.com/r/news/comments/94ssi3/80yearold_medical_marijuana_patient_with_expired/.json?limit=1","/data[0]/children[0]/subreddit,/data[0]/children[0]/title","noHeaders")

I expect to get the result = news and 80-year-old medical marijuana patient with expired card jailed for less than an eighth of cannabis

However I the get the entire data under /data[0]/children[0].

What am I doing wrong?

allenyllee · 2018-08-08T06:11:43Z

Just input one path at a time, don't use comma separate with multiple path.

So you need to separate your single request into two items: /data[0]/children[0]/subreddit and /data[0]/children[0]/title.

=ImportJSON("https://www.reddit.com/r/news/comments/94ssi3/80yearold_medical_marijuana_patient_with_expired/.json?limit=1","/data[0]/children[0]/subreddit","noHeaders")

=ImportJSON("https://www.reddit.com/r/news/comments/94ssi3/80yearold_medical_marijuana_patient_with_expired/.json?limit=1","/data[0]/children[0]/title","noHeaders")

dabros · 2018-12-23T04:56:41Z

Is there any way to use wildcards in pathing, or xpath like syntax "//name"?

#71 (comment)

https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4#gistcomment-2704820

#91 (comment)

Duartemartins · 2020-02-07T11:15:04Z

Hey I have the following Json array:

 "Topc": {
    "all": 1080,
    "all_improved": 73,
    "all_declined": 272,
    "all_difference": 0,
    "all_left": 0,
    "all_entered": 0,
    "all_unchanged": 735,
    "top3": 63,
    "top3_improved": 14,
    "top3_declined": 12,
    "top3_difference": 8,
    "top3_left": 4,
    "top3_entered": 12,

and when filtering by "/Topc/top3" I get all values beginning with top3 when I just want the first. How do I filter out the others?

allenyllee · 2020-02-08T04:50:00Z

@Duartemartins your question seems the same as #67 (comment)

Try my gist version: https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4

Duartemartins · 2020-02-10T11:40:10Z

@Duartemartins your question seems the same as #67 (comment)

Try my gist version: https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4

Thanks! Yes that works if I change the query to "/top3".

ronlut linked a pull request Feb 15, 2018 that will close this issue

Take only first value from list of returned values #68

Open

qeet mentioned this issue Feb 23, 2020

New ImportJSON Implementation #152

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Take only first value from list of returned values #67

Take only first value from list of returned values #67

ronlut commented Jan 30, 2018

tordans commented Jan 30, 2018

ronlut commented Jan 31, 2018

Hobbesball commented Feb 15, 2018 •

edited

allenyllee commented Feb 15, 2018 •

edited

Hobbesball commented Feb 15, 2018

ronlut commented Feb 15, 2018 •

edited

Hobbesball commented Feb 15, 2018

mtompkins commented Mar 12, 2018 •

edited

allenyllee commented Mar 13, 2018

mtompkins commented Mar 13, 2018

allenyllee commented Mar 13, 2018

mtompkins commented Mar 13, 2018

allenyllee commented Mar 13, 2018 •

edited

mtompkins commented Mar 13, 2018

allenyllee commented Mar 14, 2018

mtompkins commented Mar 14, 2018

loschguy commented May 4, 2018

NickB23 commented Jul 14, 2018 •

edited

allenyllee commented Jul 16, 2018 •

edited

NickB23 commented Jul 16, 2018

gitboss commented Aug 7, 2018

allenyllee commented Aug 8, 2018

dabros commented Dec 23, 2018

Duartemartins commented Feb 7, 2020

allenyllee commented Feb 8, 2020

Duartemartins commented Feb 10, 2020

Take only first value from list of returned values #67

Take only first value from list of returned values #67

Comments

ronlut commented Jan 30, 2018

tordans commented Jan 30, 2018

ronlut commented Jan 31, 2018

Hobbesball commented Feb 15, 2018 • edited

allenyllee commented Feb 15, 2018 • edited

Hobbesball commented Feb 15, 2018

ronlut commented Feb 15, 2018 • edited

Hobbesball commented Feb 15, 2018

mtompkins commented Mar 12, 2018 • edited

allenyllee commented Mar 13, 2018

mtompkins commented Mar 13, 2018

allenyllee commented Mar 13, 2018

mtompkins commented Mar 13, 2018

allenyllee commented Mar 13, 2018 • edited

mtompkins commented Mar 13, 2018

allenyllee commented Mar 14, 2018

mtompkins commented Mar 14, 2018

loschguy commented May 4, 2018

NickB23 commented Jul 14, 2018 • edited

allenyllee commented Jul 16, 2018 • edited

NickB23 commented Jul 16, 2018

gitboss commented Aug 7, 2018

allenyllee commented Aug 8, 2018

dabros commented Dec 23, 2018

Duartemartins commented Feb 7, 2020

allenyllee commented Feb 8, 2020

Duartemartins commented Feb 10, 2020

Hobbesball commented Feb 15, 2018 •

edited

allenyllee commented Feb 15, 2018 •

edited

ronlut commented Feb 15, 2018 •

edited

mtompkins commented Mar 12, 2018 •

edited

allenyllee commented Mar 13, 2018 •

edited

NickB23 commented Jul 14, 2018 •

edited

allenyllee commented Jul 16, 2018 •

edited