JSON

This page describes the JSON-specific properties that appear in the Properties tab of Source and Target gems. These settings are the same for JSON files regardless of which connection type is configured in the gem (for example, S3, SFTP, or SharePoint).

If you need details on configuring a Source or Target gem end to end (including all tabs such as Location), see the documentation for the specific file storage connection:

• Amazon S3
• Databricks
• Microsoft OneDrive
• SFTP
• SharePoint

info

You can also use the upload file feature to use JSON files. These will be stored in the SQL warehouse configured in your fabric.

Properties

Source properties

The following properties are available for the JSON Source gem.

Property	Description	Default
Description	Description of the table.	None
Multiple documents per file	Whether the file contains multiple JSON objects separated by newline.	False
Inference Data Sampling Limit	Maximum number of rows to sample for inferring the schema.	0

Target properties

The following properties are available for the JSON Target gem.

Property	Description	Default
Description	Description of the table.	None
Multiple documents per file	Whether the file will contain multiple JSON objects separated by newline.	False

Schema validation

Prophecy lets you enable schema validation for JSON files. If you provide a schema, the Infer Schema button in the Properties tab will only run successfully if the file schema matches the validation schema.

Prerequisites

To use schema validation, you need a JSON Schema file in the same directory as your source file. If your schema uses $ref to reference external schemas, those referenced schema files must also be in the same directory. Learn how to write JSON Schema files.

Use JSON schema validation to ensure JSON files conform to a predefined structure before processing.

1. In the Location tab, toggle Enable JSON Schema Validation.
2. Provide a path to the JSON file that you will use to validate against.
3. Open the Properties tab.
4. Click Infer Schema.

If the source file schema matches the validation schema, the schema will appear in the table.

If the schemas do not match, the infer schema process will fail. To troubleshoot, look for the error in the runtime logs. Here is an example error:

Failed due to error in "OrchestrationSource_0". Error: JSON validation failed against JSON schema for file /Volumes/pipelinehub/dev/json-schema-validate/users.json: 1.data: Additional property location is not allowed; 1.data: Additional property lastLogin is not allowed

The following tabs display a sample JSON file and a corresponding validation file that would run successfully.

users.json

{
  "id": 1,
  "name": "John Doe",
  "email": "john.doe@example.com"
}

users-schema.json

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "id": {
      "type": "integer"
    },
    "name": {
      "type": "string"
    },
    "email": {
      "type": "string",
      "format": "email"
    }
  },
  "required": ["id", "name", "email"]
}