How to query based on object value?

Question

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

  {
    "businessareaname": "test",
    "profile": {
      "phone": "",
      "fullname": "",
      "email": "abe-10@spatially.com"
    }
  }

I've tried querying with:

select * 
from myTable 
where profile['email'] = 'abe-10@spatially.com';

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"       | ""
                   ""
                   "abe-10@spatially.com"
   -------------------------------------
    "other one"  | "(415)884-9938"
                   "Abe Miessler"
                   "abe@test.com"

@JorgeCampos - sure I gave it my best shot. Kind of hard to do since it's not a flat table, so let me know if you're still confused at all. The first row is the header. The next two rows are data — Abe Miessler, Nov 08 '17 at 20:34
Can you try `...where unnest( profile,['email']) = 'abe-10@spatially.com';` — Jorge Campos, Nov 08 '17 at 20:53
Threw an error: `SQLActionException[UnsupportedFeatureException: unknown function: unnest(object, string_array)]` — Abe Miessler, Nov 08 '17 at 20:58
There's nothing useful at the docs page best I could find was about object types in the JDBC section (https://crate.io/docs/clients/jdbc/en/latest/#types) there the Crate Object maps to PostgreSql json type which means that `... where profile->>'email'='abe-10@spatially.com'` or something close to it should work. See it here in the postgresql doc: https://www.postgresql.org/docs/9.3/static/functions-json.html — Jorge Campos, Nov 08 '17 at 21:11
I just spoke with the CTO of crate and he told me i'm out of luck until their engineers can look into it. Kind of disappointed in the product - this is really inconvenient :-( — Abe Miessler, Nov 08 '17 at 21:15
Hoooly cr$%¨& .... for sure... to release a type without being able to use it in a query, what a mess... :( — Jorge Campos, Nov 08 '17 at 21:18

score 3 · Accepted Answer · answered Nov 08 '17 at 21:27

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='abe-10@spatially.com'});

refresh table t1;

select * from t1 where profile['email'] = 'abe-10@spatially.com';

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": "abe-10@spatially.com"} |
+-----------------------------------+
SELECT 1 row in set (0.087 sec)

Thanks for the info. I looked at the table details and found that the email column was created with: `STRING INDEX USING FULLTEXT WITH (analyzer = 'standard' )` the object column was created as `OBJECT (DYNAMIC)` . With these settings the thee bullet points you mentioned should not be a factor - correct? — Abe Miessler, Nov 08 '17 at 21:33
`STRING INDEX USING FULLTEXT WITH (analyzer = 'standard' )` is exactly what the 3rd bullet point is referring to. Since the values are now tokenized you could query with `where profile['email'] = 'abe'` or with `where profile['email'] = 'spatially.com'` — mfussenegger, Nov 08 '17 at 22:39

How to query based on object value?

1 Answers1