1

I'm trying to validate jsonb data with a check constraint to a table that has two fields, header and body, both are jsonb fields, that I expect to be arrays.

Here is an equivalent table:

CREATE TABLE "StoredReports" (
    id serial NOT NULL PRIMARY KEY,
    name text NOT NULL UNIQUE,
    header jsonb NOT NULL CHECK (jsonb_typeof(header) = 'array'),
    body jsonb NOT NULL CHECK (jsonb_typeof(body) = 'array')
);

The values at every index in body should also be arrays of the same length as the header. And I'm trying to enforce that with a CHECK constraint.

This query gives me the correct results for individual rows.

SELECT jsonb_array_length(header) = jsonb_array_length(jsonb_array_elements(body)) FROM "StoredReports"

I've tried to use array_agg to group it all and then use ALL, however it looks like it doesn't work with functions that return sets

I can do it with a sub select like this:

SELECT bool_and(t.x) FROM (SELECT jsonb_array_length(header) = jsonb_array_length(jsonb_array_elements(body)) AS x FROM "StoredReports" WHERE id = 1) t

But check constraints don't allow for sub selects.

Is there a way to create this constraint without creating a function or trigger?

Edit:

To clarify, I would like to ensure that all child arrays of the body field, have the same length as the header.

Example

With this being in the header field:

[
    { "name": "header1" },
    { "name": "header2" }
]

I would expect the following to be insertable in the body field

[
    ["field1", "field2"],
    ["field3", "field4"],
    ["field5", "field6"]
]

However this should fail, as it doesen't have length 2 on the second element of the array:

[
    ["field1", "field2"],
    ["field3"],
    ["field5", "field6"]
]
  • 1
    I'd prefer to see an example of jsonb values that would pass the constraint and one that should fail. The whole description is confusing. – ypercubeᵀᴹ Jun 1 '18 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.