Supabase•2mo ago•

9 replies

pg_jsonschema conditional validation

PostgREST

Solved🟢SQL

Hey, I can't seem to get conditional validation to work with

pg_jsonschema

pg_jsonschema

, I have tried using

dependentRequired

dependentRequired

dependentSchemas

dependentSchemas

and

if

if

statements, but none seem to be working.

For example, if I want an object with the keys

name

name

id

id

and

description

description

but I only want

id

id

and

description

description

to be required if the other one exists I have the following schema:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name",
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  "additionalProperties": false
}

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name",
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  "additionalProperties": false
}

However when running the following in my supabase postgres instance it is validating as

true

true

instead of

false

false

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  
  "additionalProperties": false
}', '{ "name": "test", "id": "test" }'::json)

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  
  "additionalProperties": false
}', '{ "name": "test", "id": "test" }'::json)

The same also applies for:

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentSchemas": {
    "id": {
      "type": "object",
      "properties": {
        "description": { "type": "string" }
      },
      "required": ["description"]
    }
  }
}', '{ "name": "test", "id": "test" }'::json)

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentSchemas": {
    "id": {
      "type": "object",
      "properties": {
        "description": { "type": "string" }
      },
      "required": ["description"]
    }
  }
}', '{ "name": "test", "id": "test" }'::json)

Json Schema validator: (Example 1, Example 2)

Is there something I am doing wrong?

pg_jsonschema conditional validation

PostgREST

Solved🟢SQL

Hey, I can't seem to get conditional validation to work with

pg_jsonschema

pg_jsonschema

, I have tried using

dependentRequired

dependentRequired

dependentSchemas

dependentSchemas

and

if

if

statements, but none seem to be working.

For example, if I want an object with the keys

name

name

id

id

and

description

description

but I only want

id

id

and

description

description

to be required if the other one exists I have the following schema:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name",
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  "additionalProperties": false
}

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name",
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  "additionalProperties": false
}

However when running the following in my supabase postgres instance it is validating as

true

true

instead of

false

false

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  
  "additionalProperties": false
}', '{ "name": "test", "id": "test" }'::json)

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" },
    "description": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentRequired": {
    "id": ["description"],
    "description": ["id"]
  },
  
  "additionalProperties": false
}', '{ "name": "test", "id": "test" }'::json)

The same also applies for:

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentSchemas": {
    "id": {
      "type": "object",
      "properties": {
        "description": { "type": "string" }
      },
      "required": ["description"]
    }
  }
}', '{ "name": "test", "id": "test" }'::json)

SELECT json_matches_schema('{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "name": { "type": "string" },
    "id": { "type": "string" }
  },
  "required": [
    "name"
  ],

  "dependentSchemas": {
    "id": {
      "type": "object",
      "properties": {
        "description": { "type": "string" }
      },
      "required": ["description"]
    }
  }
}', '{ "name": "test", "id": "test" }'::json)

Json Schema validator: (Example 1, Example 2)

Is there something I am doing wrong?

pg_jsonschema conditional validation

pg_jsonschema conditional validation

Similar Threads

Similar Threads

Similar Threads