CODEWITHSUNDEEP
sqldatabasecrate
Abe Miessler
Abe Miessler

Reputation: 85046

How to query based on object value?

I have a crate db table with records like the one below:

  {
    "businessareaname": "test",
    "profile": {
      "phone": "",
      "fullname": "",
      "email": "[email protected]"
    }
  }

I've tried querying with:

select * 
from myTable 
where profile['email'] = '[email protected]';

but nothing get's returned. How can I pull records based on an email value that is in an object?

This isn't a flat table so this is my best attempt at showing the table structure. The first row is the header and the next two rows are data.

    business name | profile:
                    - phone
                    - fullname
                    - email
    -------------------------------------
    "test"       | ""
                   ""
                   "[email protected]"
   -------------------------------------
    "other one"  | "(415)884-9938"
                   "Abe Miessler"
                   "[email protected]"

Upvotes: 0

Views: 4854

Answers (1)

mfussenegger
mfussenegger

Reputation: 3971

The example you wrote should work and is correct.

Reasons it might not work is that the table schema is not correct, specifically:

  • The email column was created with INDEX OFF
  • The object column was created with column-type IGNORED
  • There is a fulltext index / analyzer on the email column and so the email is tokenized.

Here a full working example:

create table t1 (profile object as (email string));

insert into t1 (profile) values ({email='[email protected]'});

refresh table t1;

select * from t1 where profile['email'] = '[email protected]';

If piped into crash this would output:

CONNECT OK
CREATE OK, 1 row affected  (0.286 sec)
INSERT OK, 1 row affected  (0.082 sec)
REFRESH OK, 1 row affected  (0.065 sec)
+-----------------------------------+
| profile                           |
+-----------------------------------+
| {"email": "[email protected]"} |
+-----------------------------------+
SELECT 1 row in set (0.087 sec)

Upvotes: 3

Related Questions