Bad extraction data from an API string

[SitesMasstec]

Hello colleagues!

I got the following response from an API request:
Request is:

https://olinda.bcb.gov.br/olinda/se...ataCotacao='06-01-2023'&$top=100&$format=json

Response is:

I wrote the code, using STREXTRACT function in order to extract the value following "cotacaoVenda". I shoud get the value 5.03500, but I got nothing!

My code is:

my_txtDolar = STREXTRACT(Resposta, [{"cotacaoVenda": }] )

I understood the variable my_txtDolar should return 5.03500

 

[Chris Miller]

Why the curly brackets in your STREXTRACT?

Also, more important than this detail: Why don't you get this is JSON and you should use the nfJSON library to convert this to data.

Chriss

 

[Chris Miller]

Here's what you get and how you get there, if you use Marco Plaza's nfJSON. You already did so.

First again a reminder JSON means [highlight #FCE94F]J[/highlight]ava[highlight #FCE94F]S[/highlight]cript [highlight #FCE94F]o[/highlight]bject [highlight #FCE94F]n[/highlight]otation. So it is a notation of an object, what you mainly get out of JSON is an object, and VFP can do objects:

Local  lcJSON

* I just copied the result of your URL here, you get this as a httprequest responsebody, too
Text To lcJSON noshow 
{"@odata.context":"[URL unfurl="true"]https://was-p.bcnet.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata$metadata#_CotacaoDolarDia","value":[/URL][{"cotacaoCompra":5.03440,"cotacaoVenda":5.03500,"dataHoraCotacao":"2023-06-01 13:07:17.224"}]}
EndText

* Just using the simplest PRG of nfjson to read the JS notation into a VFP object, nfjsonread.prg:
loJS = nfjsonread(lcJSON)
Suspend && suspend the program and take a look at the object in the Locals window of the debugger...

And this is what I see in the debugger:

You see, even without knowing how this ends up, the debugger gives you an explorable treeview showing an object and its hierarchy with its properties, in this cas it mainly has an array property. Also, pay attention to the columns value and type of the Locals window. That shows you what I just said.

It's all there, you just have to use it again.

Finally, you see you get this value from

loJS.Value[1].cotacaoVenda

And it's not just the string, it is even the number, already.

Chriss

 

[SitesMasstec]

Chriss:

I haven't used Marco Plaza' nfJson yet, because of the complexity. But I am sure I will have to use it. For now I have solved, with the help of you colleagues, my first problem using a response from an API, about two weeks ago:

Response from API

https://api.postmon.com.br/v1/cep/13288-172:

{"bairro": "Distrito Industrial Benedito Storani", "cidade": "Vinhedo", "logradouro": "Rua Iracema Lucas", "estado_info": {"area_km2": "248.221,996", "codigo_ibge": "35", "nome": "S\u00e3o Paulo"}, "cep": "13288172", "cidade_info": {"area_km2": "81,604", "codigo_ibge": "3556701"}, "estado": "SP"}

 Resposta = xmlHttp.responseText
 thisform.txtCende.SetFocus

* Reading variables
my_logradouro = STREXTRACT(Resposta, ["logradouro": ], [,] )
my_bairro = STREXTRACT(Resposta, ["bairro": ], [,] )
my_cidade = STREXTRACT(Resposta, ["cidade": ], [,] )
my_estado = STREXTRACT(Resposta, ["estado": ] )

* Take asteristics away
my_logradouro = SUBSTR(my_logradouro,2,(LEN(my_logradouro)-2))
my_bairro = SUBSTR(my_bairro,2,(LEN(my_bairro)-2))
my_cidade = SUBSTR(my_cidade,2,(LEN(my_cidade)-2))
my_estado = SUBSTR(my_estado,2,2)

So, I think there is a solution using STREXTRACT, but I haven't figure out how, using STREXTRACT, to extract "cotacaoVenda" from the string {"@odata.context":"

https://was-p.bcnet.bcb.gov.br/olin.../v1/odata$metadata#_CotacaoDolarDia","value":

[{"cotacaoCompra":5.03440,"cotacaoVenda":5.03500,"dataHoraCotacao":"2023-06-01 13:07:17.224"}]}


Thank you,
SitesMasstec

 

[SitesMasstec]

Solved:

my_txtDolar = STREXTRACT(Resposta, '"cotacaoVenda":', ',"', 1)

The result was the expected: 5.03500


Thank you,
SitesMasstec

 

[Chris Miller]

SitesMastec,

you're very forgetful.

See your last post in thread184-1821360, from 12 May 23 18:52.

You posted code that includes this line:

vfpobj = [highlight #FCE94F]nfJsonRead[/highlight](Resposta)

You're not doing yourself a favor by ignoring and even rejecting what you already used. That's actually outrageous of you.

nfjsonread.prg is really a general-purpose solution for any JSON. There even is an inbuilt conversion of Unicode as far as it can be converted to the characters ANSI also contains, which was the reason you used it for, then. And it even includes resilience against names VFP does not support, like the name of the property "@odata.context" that nfjson transposes to __odata_context. And it converts data to the types they actually are.

You have everything you need in one call, not just one of the details of this JSON string, you get all the details in one call.

Chriss

PS: I'm fine with whatever you end up using, ou're free to make a bad choice, when it still works for you, but don't tell you never used nfjso so far, when it's provable you did already.

 

[SitesMasstec]

Yes, Chriss, I use Marco Plaza's nfJason procedure in my other application and it works fine.

But I tried in this other application and it gives an error, so I tried the STREXTRACT funcion to get the result I need.

Please see nfJason in this application:

xmlHttp.open("GET", DtEmissaoparaAPI, null)

xmlHttp.setRequestHeader("Content-Type", "text/xml")
xmlHttp.send()     

Resposta = xmlHttp.responseText

vfpobj = nfJsonRead(Resposta)
my_txtDolar = vfpobj.cotacaoVenda

This error appear:

Thank you,
SitesMasstec

 

[Chris Miller]

And this makes you give up? The JSON isn't as flat as in your other case. But when something is part of the JSON string, it is always also inside the object.

I showed you how to use the debugger to find out. And when you use vfpobj as your object name, it becomes vfpobj.Value[1].cotacaoVenda

What has the property you look for will always depend on what the JSON represents. And even if you don't know, you can see it from the treeview the debugger provides for object variables. In this case the JSON does not represent an object of properties, but an object that has an array of objects.

If you always give up after a first try to get at something you won't get far, especially in programming.

Chriss

 

[Chris Miller]

I know it's solved, to anyone who cares about how good such a STREXTRACT solution is, you don't have to know details about JSON to understand it and the implications of trying to extract a single bit of information from JSON only. JSON, unlike XML or more generally SGML notations, has no concept of begin and end delimiter. You can take the name of some object property in quotes followed by a colon as a beginning delimiter, that's okayish. But JSON also is correct, if there are spaces between the quoted name and the colon. You don't usually face the problem of any API giving you arbitrary changes of formatting like whitespace changes, so you could argue once the beginning delimiter for STREXTRACT is exactly '"cotacaoVenda":', it stays that and there's no problem using that. I'll not criticize that.

One problem that could arise is that there is no defined end delimiter, though. That doesn't mean the JSON can continue with anything, but it can vary. There are mainly two alternatives: There is a next property/element, separated by a comma from cotacaoVenda, or there is no further and the JSON part ends in a closing curly bracket }. Well, or whatever closing bracket needed to end what cotacaoVenda is part of. That could also be a closing square bracket ]. Again, once an API gives some result structure, that won't change with every single request. But you never know. At least I sense a little understanding from using the comma and the quote " character of the next element as end delimiter, because using only the comma would mean you could interpret a comma within the value of the element you want to extract as the end of the value. In case of textual elements, a comma can clearly be part of a text. In this case, there is no comma to be expected as it's a numerical value and even the usage as thousands separator is not foreseen in JSON. But to make it extremely clear, you are using a hack to detect the end of the cotacaoVenda element that depends on there being a next element.

For the reason of that structural composition of JSON, a more holistic approach is to always parse the whole JSON, even if you're only interested in a single value from it. Otherwise, you can never ensure with certainty you cut off part of the value or you get parts not belonging to the value. In this specific case, imagine the dataHoraCotacao element of the data would be dropped, then that STREXTRACT would result in nothing. And leaving out the end delimiter completely you'd get the number followed by }]} or whatever else comes after the actual value. And the number and type of closing brackets then would also be subject to change.

In very short:
As JSON isn't as easily parsed as XML or other SGML types of notation, you better parse it fully and find the location of the information you want to extract by checking out a sample. This is also subject to change, but there we are in the usual sense of the "subject to change" problem as anything can change its own name or even be dropped or repositioned after a refactoring of code and data structures. This is not the type of "subject to change" and fail, because of a change, against which a wholesome parsing would be stable.

In XML you don't always have the beginning and end delimiters in the form of <tag> and </tag>, too. Because as a variation of that, self-closing tags exist. Those have the structure <tag attribute="value" /> ending in />. That's possible, if they are not a tag type that needs to embed further inner SGML.

Overall, take the recommendation to use the right tool for the job, and that's not STREXTRACT here. While I think nfJSON will make use of STREXTRACT, too, it works quite more complicated, but it works very well and knows the context of the current string position of parsing the JSON. If you think you make it faster and easier to maintain without the dependency of that nfjsonread.prg, that's only half the truth. That's what I want to point out about such hacks.

The other point I don't want to drive that deep is, that you underestimate the complexity JSON can have, if you expect conversion to (VFP) objects always will have a flat structure with all the elements you see in the JSON as elements. Here you have an unusual structure that is an object with an array property that only has 1 element, which is the actual object. You think too simple about JSON, if you think nfJSON fails, because you don't find vfpobj.cotacaoVenda. It's deeper in, that's all.

It is an overcomplicated JSON for what it represents, which illustrates the IT incapabilities of the provider of this JSON. That's just pure opinion, though, and I'm aware this is a governmental API. They will have their reasons - it's all part of more complex data in the first place. But it's not the first time I see something that grotesque coming from a governmental API. It's disconcerting that on the one side, they have their intelligence services up to a point of extreme capabilities of surveillance, but they can't provide the simplest form of data for something that is far less complex. Well, again, that's just my opinion about that. You see, I have too much time on my hand and should care less about things I could care less about.

I reember I once recommended STREXTRACT myself as "scalpel tool in the string functions belt of VFP" in thread184-1817900. There it was in the context of XML which has the tag system of beginning and end delimiters, whereas the role of that in JSON is spread and shared by commas and several bracket types on top of string delimiters, which makes it far more compact than XML. It needs context-sensitive parsing, which isn't possible by the simplicity of STREXTRACT. Not as the only tool, at least.

Chriss

 

[SitesMasstec]

Hello Chriss!

I am still trying to use JSON to get the data from that government API. But for now I am using the STREXTRACT to have the problem solved.


In my other application (Postmon) I use JSON, but I also tested XML approach. Both are ok.

m.XML = m.HTTP.responseXML
my_logradouro = m.XML.selectNodes("result/logradouro").item(0).text
my_bairro = m.XML.selectNodes("result/bairro").item(0).text

Thank you,
SitesMasstec

 

[Chris Miller]

Hello SitesMasstec,

the solution for this JSON is at hand already, I don't know why and what exactly you're still trying. I already posted twice once you use nfjsonread on that JSON you get the cotacaoVenda from

objectname.Value[1].cotacaoVenda

And objectname is whatever you use as name of the result object that's your choice.

What I can say about the XML approach is that the XMLHTTPobject offers more than just the XML string in case the result is XML, it offers an XML object that includes methods like selectNodes, which are capable to extract what ou need. For that reason XML is working better as it has more functionality in the XMLHTTP class, but that's not making it better in all aspects, XML is usually more verbose.

And you can't always get XML from any API, it's the decision of whoever provides the API whether reults are provided in XML form or JSON or even something else, there are many more formats starting with as simple as CSV or even binary formats.

You always know what your response is from looking into xmlHttp.getResponseHeader('Content-Type') and in case of the other API you could decide for XML by using the query parameter. That's not always given. So it's better to be able to parse what you get.

Chriss