Skip to main content
To homepage To homepage

Hi,

I have a JSON data source that looks something like this

{
    "company": {
        "companyId": 1,
        "name": "AdventureWorks",
        "description": ""
    },
    "hierarchies":  
        {
            "depths":  
                {
                    "depth": 0,
                    "name": null
                },
                {
                    "depth": 1,
                    "name": "Company"
                }
            ],
            "allKeys": "1|-1|10"
        }
    ],
    "slicer": null,
    "userToGroup": "Username,GroupId\nJohn,G1\nSarah,G1\nPeter,G1\nKate,G1",
    "groupToKey": "GroupId,Key\nG1,10\nG1,101\nG1,102\nG1,103\nG1,104"
}

And I am only really interested in the last two objects, userToGroup and groupToKey, which both are in a CSV format. Is it possible to use something like xPath to navigate to these two objects and then parse them as CSVs? 

I have an RSD file that gives me userToGroup:

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
  <api:info title="hierarchies" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <!-- You can modify the name, type, and column size here. -->
	"userToGroup"
    <attr name="json.userToGroup"                xs:type="string"  readonly="false"              other:xPath="/json/userToGroup"                 />
  </api:info>

  <api:set attr="DataModel" value="DOCUMENT" />
  <api:set attr="URI" value="http://api.com/api/1" />

  <api:set attr="JSONPath" value="$.hierarchies" />

But everything ends up in a single column/row. 

Does anyone have any experience working with this sort of JSON/CSV format combination?

Hi Pontus

To make a RSD file that can both use CSV and JSON in one will be rather difficult.

I would instead suggest to do something similar to what I do in this project.

I have all the data in one field, so both names and content.

So this

Becomes this

 

Hi,

you should be able to split the strings using String formatter expressions (see: https://cdn.cdata.com/help/DJJ/ado/pg_stringvalueformatters.htm) as long as the strings have a somewhat predictable structure. It might also be possible to use the regex string formatters to convert the mini-csv into a json array and have the connector deal with pulling that apart. 

Whether or not you find .rsd magic easier / better than doing the string splitting in SQL is a matter of taste, though I don't like hiding logic in .rsd much.

Hi, 

Thanks for the advice. I took inspiration from Thomas’ project and did the string splitting in SQL. It is indeed more visible to have the logic in scripts in TimeXtender, rather than hidden away in .rsd files.

Reply


Terms and ConditionsCookie settings