Syntax API JSONPath - pull last line, 2nd value - EDITED

I’m looking to pull the last line, 2nd value, from this API.

Can someone provide a general JSONPath Syntax to get the last line, 2nd value?

EDITED - The last value changes every day, a new entry is added.

colintalkscrypto.com/cbbi/data/latest.json

Best, Jay

Assuming it’s valid JSON, the path is simple:

$.Confidence.1737504000

But you provide no information about what this json is. Does that last entry always start with “1737504000”? If not there might be a way to do it using the implementation of JSONPATH OH provides. Someone else will need to provide that. The problem is it’s not an array so you can’t use array operations (e.g [-1]).

If I were doing this I’d either figure out a REFEX or use a JS transformation.

var parsed = JSON.parse(input);
var key = parsed.Confidence.keys()[parsed.Confidence.length-1];
parsed.Confidence[key];

I’m sure there are other ways to get the value of the last element of a map. I’m assuming the keys are returned in order. If not you need to sort the keys before getting the last one.

Thanks Rich, I can always count on you. I did make a mistake by not telling you the last value changes every day (new entry is added to the end).

Best, Jay

so that last entry is epoch time stamp so you could calculate that and pull result based on it since it is a predicable value so tomorrow you should see the last entry as 1737590400:some value
since each one of the previous is based on GMT : day name, month name day of month , year hour : minute : second AM or PM
thus 1737504000 is converted to GMT : Wednesday, January 22, 2025 12:00:00 AM
and

Yr Mon Day
- -
Hr Min Sec
: :

Human date to Timestamp

Epoch timestamp: 1737590400
Timestamp in milliseconds: 1737590400000
Date and time (GMT): Thursday, January 23, 2025 12:00:00 AM
Date and time (your time zone): Wednesday, January 22, 2025 7:00:00 PM GMT-05:00

I was assuming it wasn’t that regular.

In a JS transformation that could look something like:

JSON.parse(input).Confidence[String(time.toZDT("00:00").plusDays(1).getLong())]

It might also work to use “24:00” instead of plusDay.

Justan,

Thx u so much for catching this!

Can you provide the DSL syntax for the epoch string so I can use it in a JSONPath?

Best, Jay

JRuby transform syntax:

RB(|require "json"; logger.info JSON.parse(input)["Confidence"].to_a.last.last)

This gives you 0.8032

You can call it from RulesDSL like this:

      val src = sendHttpGetRequest("https://colintalkscrypto.com/cbbi/data/latest.json")
      val output = transform("RB", "|require 'json'; JSON.parse(input)['Confidence'].to_a.last.last", src)
      logInfo("XX", "output: {}", output)

You’d need to install jrubyscripting addon

1 Like

I wanted to thank @jimtng , @rlkoshak , @justaoldman for taking the time to come up with solutions.

I ended up choosing @jimtng because it removed the variable of getting the epoch value. His always goes to the last row by default.

You guys are great to provide many options to tackle my problem.

Best, Jay