How to filter based on null value using REST API

#1

Hi there,

I’m trying to filter the records based on a field that has a null value via rest API

any ideas?

#2

@tjerman any ideas here?

#3

You can use field IS NULL and field IS NOT NULL here.

So a field has two states – non-null (when you set any value to it) and null (when the field doesn’t have anything in it).
This can be a little catch when working with boolean fields (it has true, false, and null).
You can remove the null state by configuring the field to have a default value.

#4

@tjerman is it case sensitive?

#5

I can’t recall; I think it is not but you can try both and let me know

#6

@tjerman I think I’m facing an issue here

I have a boolean field called is_admin
and I’m trying to do something like this
"query=user_id=123 and is_admin != true"

because I’m interested in records that have is_admin field false or null
this is doesn’t work!
also, I wonder how to do AND like this
"query=user_id=123 and (is_admin = false or is_admin is null)"

thank you in advanced

#7

Hmm… could that user_id=... be the issue – filtering out everything already?

I just did a quick little test and this is how it went:

Screenshot from 2022-02-07 18-33-28
Screenshot from 2022-02-07 18-33-28758×311 14.1 KB

A record list for where the good field indicates a boolean; y = true, n = false, nothing = NULL.

Note the | pp_json in the cURL requests below; remove it if you don’t use zsh with the JSON pretty print extension.

Running this to get all records where field is null:

curl "$INSTANCE/api/compose/namespace/270156926220791811/module/270156975059267587/record/?query=good+IS+NULL"\
  -H 'accept: application/json, text/plain, */*' \
  -H "authorization: Bearer $JWT" \
  --compressed | pp_json;

returns this:

{
    "response": {
        "filter": {
            "moduleID": "270156975059267587",
            "namespaceID": "270156926220791811",
            "query": "good IS NULL",
            "deleted": 0,
            "sort": "id"
        },
        "set": [
            {
                "recordID": "270157079229132803",
                "moduleID": "270156975059267587",
                "values": [
                    {
                        "name": "name",
                        "value": "c"
                    }
                ],
                "namespaceID": "270156926220791811",
                "ownedBy": "252451468215349252",
                "createdAt": "2022-02-07T17:29:27Z",
                "createdBy": "252451468215349252",
                "canUpdateRecord": true,
                "canReadRecord": true,
                "canDeleteRecord": true,
                "canGrant": true
            }
        ]
    }
}

And running this:

curl "$INSTANCE/api/compose/namespace/270156926220791811/module/270156975059267587/record/?query=good+IS+NULL+OR+good=false" \
  -H 'accept: application/json, text/plain, */*' \
  -H "authorization: Bearer $JWT" \
  --compressed | pp_json;

returns this:

{
    "response": {
        "filter": {
            "moduleID": "270156975059267587",
            "namespaceID": "270156926220791811",
            "query": "good IS NULL OR good=false",
            "deleted": 0,
            "sort": "id"
        },
        "set": [
            {
                "recordID": "270157071679254531",
                "moduleID": "270156975059267587",
                "values": [
                    {
                        "name": "good"
                    },
                    {
                        "name": "name",
                        "value": "b"
                    }
                ],
                "namespaceID": "270156926220791811",
                "ownedBy": "252451468215349252",
                "createdAt": "2022-02-07T17:29:23Z",
                "createdBy": "252451468215349252",
                "canUpdateRecord": true,
                "canReadRecord": true,
                "canDeleteRecord": true,
                "canGrant": true
            },
            {
                "recordID": "270157079229132803",
                "moduleID": "270156975059267587",
                "values": [
                    {
                        "name": "name",
                        "value": "c"
                    }
                ],
                "namespaceID": "270156926220791811",
                "ownedBy": "252451468215349252",
                "createdAt": "2022-02-07T17:29:27Z",
                "createdBy": "252451468215349252",
                "canUpdateRecord": true,
                "canReadRecord": true,
                "canDeleteRecord": true,
                "canGrant": true
            }
        ]
    }
}

The above shows it should work as expected.
This was done on 2021.9.7-rc.1 but it should be the same for as long as I recall.

Are you using MySQL or PostgreSQL?
Perhaps there could be an issue there.

How to do partial update via rest api
#8

@tjerman thank you for your continuous efforts

I noticed something in your URLs, you replace the space with + sign
I just put space !! does it mentioned somewhere in the docs??

so, I think that’s the issue (I’ll try it once I’m at work)
regarding your last question, I’m using Postgresql

thanks again really appreciate it

#9

Well yeah; when you’re making requests manually you usually need to make sure the text is properly encoded so that it can be passed through.
Higher-level API client libraries usually do the encoding automagically so you don’t need to worry about it.

Spaces are encoded as %20 where + can be used as an alternative and since it makes it clearer to read I usually use +.

When some request fails for no apparent reason it could be due to improper encoding – the easiest way to figure out is to compare your query with the one returned by the server.
When testing larger queries I usually tend to write it down in plain text and then run it through the encodeURI JS function.

1 Like