Nested structures/ JSON

[jim108dev]

Hi! Oftentimes the object structure is nested while the database result is flat, for example

{
  "_id": "string",
  "email": "string",
  "name": {
    "first": "string",
    "middle": "string",
    "last": "string"
  },
  "picture": "string",
  "role": "none",
  "userStatus": true,
  "dateOfBirth": "2021-02-14",
  "address": {
    "line1": "string",
    "line2": "string",
    "city": "string",
    "state": "string",
    "zip": "string"
  },
  "phones": [
    {
      "type": "none",
      "digits": "string"
    }
  ]
}

If I don't save the nested structures as JSON, I find myself creating a flat and a nested structure and doing some laborious mapping

mkUser :: Entity -> User
mkUser r =
  { _id: r._id
  , email: r.email
  , picture: r.picture
  , role: r.role
  , dateOfBirth: r.dateOfBirth
  , userStatus: r.userStatus
  , address:
      { line1: r.line1
      , line2: r.line2
      , city: r.city
      , state: r.state
      , zip: r.zip
      }
  , name:
      { first: r.first
      , middle: r.middle
      , last: r.last
      }
  ...
  }

If I do save the nested structure as JSON, I loose the ability to type-safe search, right? For instance search for users with city xy. Or is there a better solution to that?

[Kamirus]

Interesting case, I have not thought about it yet. Here are some hints that might help:

As I understood it would be nice to have records as db values/columns. Let's try defining the users table:

users :: Table
  ( name :: { first :: String, middle :: String, last :: String }
  , ...
  )

Assuming name is a column with type json in the schema - or some equivalent record-like type (so that we could do name.first or name::json->'first' or whatever is the correct PG syntax for accessing the first).

But now we have a problem when we want to access first:

selectFrom users \user -> do
  -- user.name is of type `Col s { first :: String, middle :: String, last :: String }`
  restrict $ user.name.first .== lit "xyz" -- user.name is not a PS record, problem

user.name is not a record - it is Col s <record>. user.name in the generated query corresponds to a column named name. So to get to the first field we need a custom expression that takes name and returns name.first in the generated query (or name->'first').

getFirst :: Col s { first :: a | r } -> Col s a
getFirst record = Col $ Any do
  rec <- showCol record
  pure $ rec <> ".first" -- or `->'first'`, or whatever is the PG way of accessing json/record

It is possible to provide a more general function get for accessing any field of the record (that would work for any label not only first). Then it should be just: ``user.nameget` .firstoruser.name get (SProxy :: SProxy "first")```_

selectFrom users \user -> do
  -- user.name is of type `Col s { first :: String, middle :: String, last :: String }`
  restrict $ getFirst user.name .== lit "xyz"

The other problem is serialization: You would need to provide postgresql-client instances: ToSQLValue and FromSQLValue for any record and this is not possible without changing postgresql-client itself as these would be orphan instances 😞

A workaround would be to create a newtype for Record newtype MyRecord r = MyRecord (Record r) And then replace { first :: a | r } with MyRecord ( first :: a | r ) and { first :: String, middle :: String, last :: String } with MyRecord ( first :: String, middle :: String, last :: String )

---

This is definitely worth exploring Let me know if the idea is clear and if it was helpful 😃

[jim108dev]

Ah, I see, making some newtype

newtype Address
  = Address
  { city :: String
  , zip :: Int
  }

derive instance genericAddress :: Generic Address _

instance showAddress :: Show Address where
  show = genericShow

derive newtype instance readForeignAddress :: Json.ReadForeign Address
derive newtype instance writeForeignAddress :: Json.WriteForeign Address

instance fromSqlValueAddress :: FromSQLValue Address where
  fromSQLValue :: Foreign -> Either String Address
  fromSQLValue = lmap show <<< Json.read

instance toSQLValueProductType :: ToSQLValue Address where
  toSQLValue = unsafeToForeign

type UserRow c auto
  = ( id :: c (auto Int)
    , address :: c Address
    )

type Identity a
  = a

type User
  = { | UserRow Identity Identity }

type UserTable
  = Table (UserRow Identity Auto)

userTable :: UserTable
userTable =
  Source "user"
    $ case _ of
        Nothing -> "\"user\""
        Just alias -> "\"user\"" <> " " <> alias

type UserCols s
  = { | UserRow (Col s) Identity }

and creating an individual function for the particular attribute is a solution. Thanks.

select :: forall s. FullQuery s (UserCols s)
select =
  selectFrom userTable \r -> do
    restrict $ (getCity r.address) .== lit "Boston"
    pure r

getCity :: forall s. Col s Address -> Col s String
getCity obj =
  Col
    $ Any do
        sObj <- showCol obj
        sAttr <- showCol $ litPG "city"
        pure $ sObj <> " ->> " <> sAttr