Pipe JSON Services to CSV in PowerShell

The CData Cmdlets Module for JSON is a standard PowerShell module offering straightforward integration with JSON. Below, you will find examples of using our JSON Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your JSON Services

See the Getting Started chapter in the data provider documentation to authenticate to your data source: The data provider models JSON APIs as bidirectional database tables and JSON files as read-only views (local files, files stored on popular cloud services, and FTP servers). The major authentication schemes are supported, including HTTP Basic, Digest, NTLM, OAuth, and FTP. See the Getting Started chapter in the data provider documentation for authentication guides.

After setting the URI and providing any authentication values, set DataModel to more closely match the data representation to the structure of your data.

The DataModel property is the controlling property over how your data is represented into tables and toggles the following basic configurations.

• Document (default): Model a top-level, document view of your JSON data. The data provider returns nested elements as aggregates of data.
• FlattenedDocuments: Implicitly join nested documents and their parents into a single table.
• Relational: Return individual, related tables from hierarchical data. The tables contain a primary key and a foreign key that links to the parent document.

See the Modeling JSON Data chapter for more information on configuring the relational representation. You will also find the sample data used in the following examples. The data includes entries for people, the cars they own, and various maintenance services performed on those cars.

$conn = Connect-JSON  -URI "$URI" -DataModel "$DataModel"

Selecting Data

Follow the steps below to retrieve data from the people table and pipe the result into to a CSV file:

Select-JSON -Connection $conn -Table people | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\mypeopleData.csv -NoTypeInformation

You will notice that we piped the results from Select-JSON into a Select-Object cmdlet and excluded some properties before piping them into an Export-Csv cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each "row" in the result set, and we do not necessarily want that information in our CSV file.

Deleting Data

The following line deletes any records that match the criteria:

Select-JSON -Connection $conn -Table people -Where "[ personal.name.last ] = Roberts" | Remove-JSON

Inserting and Updating Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into JSON, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MypeopleUpdates.csv | %{
  $record = Select-JSON -Connection $JSON -Table people -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-JSON -Connection $json -Table people -Columns ("[ personal.name.first ]","[ personal.name.last ]") -Values ($_.[ personal.name.first ], $_.[ personal.name.last ]) -Where ("Id = `'"+$_.Id+"`'")
  }else{
    Add-JSON -Connection $json -Table people -Columns ("[ personal.name.first ]","[ personal.name.last ]") -Values ($_.[ personal.name.first ], $_.[ personal.name.last ])
  }
}

As always, our goal is to simplify the way you connect to data. With cmdlets users can install a data module, set the connection properties, and start building. Download Cmdlets and start working with your data in PowerShell today!