PostgreSQL JSONB column type for arrays

Is it possible to use JSONB column type for arrays?

Vapor throw an error: "invalid field: users type: Array error: Unexpected data type: JSONB. Expected array.""

It is, though docs are pretty sparse. What does your model look like and what does your migration look like?

In Vapor 4?

Model like this:

struct Hobby: Codable {
    var title: String
}

struct User {
    var name: String
    var hobbies: [Hobby]
}

Vapor suggest using json[ ] or jsonb[ ] in postgres-kit (PostgresDataDecoder.swift):

    func unkeyedContainer() throws -> UnkeyedDecodingContainer {
        guard let data = self.data.array else {
            throw Error.unexpectedDataType(self.data.type, expected: "array")
        }
        return _UnkeyedDecoder(data: data, json: self.json)
    }

Is this a Vapor 3 or Vapor 4 project?

Vapor 4

Right so you should have a class that conforms to Model and something that conforms to Migration - what do they look like?

This code works fine only with JSONB[ ] type ( .array(of: .json) )
–°hanging the column type to JSONB ( .json ) throws an error.

final class User: Content {
        
        let name: String
        
        init(name: String) {
            self.name = name
        }
    }

    final class Todo: Model, Content {
        static let schema = "todos"
        
        @ID(key: .id)
        var id: UUID?

        @Field(key: "title")
        var title: String
        
        @Field(key: "assigned")
        var users: [User]

        init() { }

        init(id: UUID? = nil, title: String, users: [User]) {
            self.id = id
            self.title = title
            self.users = users
        }
    }

    struct CreateTodo: Migration {
        func prepare(on database: Database) -> EventLoopFuture<Void> {
            return database.schema("todos")
                .id()
                .field("title", .string, .required)
                .field("users", .array(of: .json) , .required)
                .create()
        }

        func revert(on database: Database) -> EventLoopFuture<Void> {
            return database.schema("todos").delete()
        }
    }

Hmm that kind of makes sense because you're storing an array of users, rather than just a user.

I don't think there's a good workaround unfortunately if you need the column to be just JSONB, you might need to use a custom Postgres Encoder/Decoder

Do you mean custom init(from decoder: Decoder) and public func encode(to encoder: Encoder) in model class?

No sorry, when you set up the database with something like:

app.databases.use(.postgres(
  hostname: databaseHostname,
  username: databaseUsername,
  password: databasePassword,
  database: databaseName
), as: .psql)

There's an argument for pass in a PostgresDataEncoder and PostgresDataDecoder to .postgres(). However if you're going down this route you're probably fighting the system a bit too much (though not necessarily). Does it matter if you use .array(of: json))?

Yes, because I need to use an existing database...

Ah, yeah that would make it necessary. What's the column in the DB declared as? And you get the above error when trying to connect to the existing DB?

No, I'm getting this error while reading the db.

Sorry yeah you get the error reading the existing DB? What's the column type in the existing DB?

Are you kidding me? :wink:
JSONB column which contains array of objects.

Ok if yo can raise an issue on GH it can be tracked and looked into. Fluent should definitely handle it correctly if existing tables work like that

Terms of Service

Privacy Policy

Cookie Policy