Reformat ScVals for JSON compatibility

[sydneynotthecity]

What problem does your feature solve?

Fields that contain stringified ScVals do not behave as expected. When a user attempts to traverse the "JSON" object, they are malformatted and the user is unable to use JSON functions to parse keys/values or reference elements in an array.

This is a painful experience because it requires the user to manipulate a string. This leads to brittle code that is not maintainable, and we should just fix the formatting of the stringified ScVals so that they can be loaded as a well formatted JSON object.

What would you like to see?

ScvVec and ScvMap doesn't Sprintf nicely: https://github.com/stellar/go/blob/master/xdr/scval.go#L249-L258

An example of this is key_decoded and val_decoded in contract_data. Currently, val_decoded will output:

{"type":"Map","value":"[{collateral [{1 2386457777}]} {liabilities []} {supply []}]"}

Notice that value is not a usable array or map. It should be reformatted to:

{"type":"Map","value":"[{"collateral": "[{"1": "2386457777"}]"} {"liabilities": "[]"} {"supply": "[]"}]"}

so that users can access individual attributes easier. My expectation is that to fetch the collateral from Hubble, it should be as simple as the following:

select val_decoded.value[0].collateral as collateral
from contract_data

Currently, to access this data, the SQL requires a lot of string manipulation:

select 
    -- original column definition for collateral, either works
    -- substr(trim(split(json_value(val_decoded, '$.value'), ']} {')[0], '[{collateral'), 3) as collateral,
    trim(split(split(json_value(val_decoded, '$.value'), 'collateral [')[1], ']')[0]) AS collateral,
from current_state

What alternatives are there?

Leave the string as is and require users to reformat in downstream system, like Hubble. This problem seems to stem from stellar-etl and should be fixed at the source.

Users could write UDFs using the stellar-base library and parse contract_data_xdr to get the same information. I think it's unreasonable to expect end users to write a js script within a SQL query to parse data correctly. This also places the expectation on other analytics platforms, like Dune, to support the same UDF, which may not be the case.

[chowbao]

This does not include backfilling and fixing history. That will be done in a follow up ticket