Read JSON

[Doug Easterbrook]

hi Xavier

Json is an array structure where data can be nested somewhat deeply. i .e, a field can be a name value pair, or it cane be a structure under neath that.

so, if you want to access it, you need to:
1) know/understand the structure of the data in the json
2) use tools that let you access the json data.


In omnis, you need to convert the json to rows using $jsontolistorrow .    Don’t fight it.  Thats how you do it.

Each column in the row has a name (like Color)) and a value (like red)

A column in the row, could contain a structure, in which case, you need to also use $jsontolistorrow to get to that data


eg, the ‘Color’ column could contain MULTIPLE colours like ‘red, blue, green’ in its own array.

knowing that, you might need to extract the ’color’ data out of that column into its own row or list to see all the values.



anyhow.     its work, but you need to do it.  There is no simple magical answer, especially if you are intent on updating the data (like adding another colour to the ‘color’ column.   so use the omnis OJSON tools to do it.         



If you are using postgres (or other db with JSON fields) and this field comes from your database, it is possible to extract the data values directly using some jsonb primitives in the database, such as this

select payr_merchant_data::json->'merchant'->'receipt'->>’TxnName’ as TransactonReceiptType
from payr_data

(which looks at the field ‘payr_merchant_data which is known to. be a json string.  The ‘merchant’->Receipt’->>’TxnName’ would be a 3 level nested JSON data that we expect to have a value.    The select statement transverses the JSON string to find that nested value and returns it as ‘TransactonReceiptType’


if you simple read that multi level JSON string into omnis, you would have to use 3 ‘$jsontolistorrow’ to get te value.

ie.

step 1:   get the first level values into a row ‘A'
step 2:  look at the ‘merchant’ column in row ‘A'
step 3:  convert merchant column into a row ‘B'
step 4: look at the ‘receipt’ column in row ‘B’
step 5:  convert the receipt column to row ‘C’
step 6:  look at the ’TxnName’ column in row ‘C’ and the value could be saved into a local variable ‘TransactonReceiptType’



sorry to bear the bad news.    its better to do it right.




Before oJson was available to all, we wrote an external called JsonCPP which we freely give away at Euromnis (or to anybody who wants it).   IT was written in a differnent way that the OJSON omnis comopent.      Ojson, to my understanding makes you decompose each JSON row into another row and do that recusively till you get what you want.

JsonCPP is designed to suck the json into the component and then it lets you access the values in the array, much like postgres functions set what you want.

eg.

Do jsonValue.$get('params').$get('allow_order_in_progress').$set(boolValue)


which, if you pulled your json data into the jsoncpp external, you could refer to color as 


move TX_CONFIG into jsonCPP external cobject where called ‘jsonValue' (its one liner)

calculate color as jsonValue.$get(‘color’)

and then you can get the color directly.



its a long winded answer…. the summary is three points

if the data is json, deal with it as a json structure, don’t try to kludge the answer.  Either use Omnis’s oJson or make some more convenient way of getting it from oJson.    As I said, we wrote our own long time back and it mirrors how other tools use ‘dot’ notation to get into the heirarchy.

Json can have multiple embedded data, so you have to recursively extract the data

You have to know the data structure of the JSON field, much like a data record, and write your code accordingly.

If you do it right, then the JSON is really really powerful way to store things in a single field

and if its a database field, you can get your SQL database to decompose it for you, if you tell the SQL engine to treat a character field as JSON  (or ig you store it as a JSON field type in the dabase).    Thats another topic for euromnis.






Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Sep 16, 2024, at 1:53 AM, IT <it at plastipol.com> wrote:
> 
> Hi,
> 
> I have a JSON field TX_CONFIG with a pair data {“color”:”kRed”}
> 
> 
> I’m unable to read “color” directly from the JSON. The only way is to convert to a row and then read from the row to assign the value.
> 
> 
> Do OJSON.$jsontolistorrow(ivFACE.TX_CONFIG) Returns lvColor
> Calculate $cinst.$objs.ivFAE_estado.$textcolor as [lvColor.color]
> 
> 
> Is there a way to read the value of a JSON directly without the need of an intermediate row? Beyond that you need two lines of code, you need previously to define a row field to store the JSON.
> 
> Xavier
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com