I want to use document path.

[joanney1]

I complied "webscalesql-5.6.24.97"
And I changed system variables

mysql> show variables like '%son%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| end_markers_in_json | ON |
| use_fbson_input_format | ON |
| use_fbson_output_format | ON |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show variables like '%document%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| allow_document_type | ON |
+---------------------+-------+
1 row in set (0.00 sec)

then,

mysql> show create table tt;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE tt (
id int(11) NOT NULL,
doc document NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id_doc (id,doc.address.zipcode AS INT)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into tt values (100, '{"name":"Tom","age":30,"married":false,"address":{"houseNumber":1001,"streetName":"main","zipcode":"98761","state":"CA"},"cars":["F150","Honda"],"memo":null}');
Query OK, 1 row affected (0.00 sec)

mysql> select id, doc.name from tt where doc.address.zipcode like '98761';
Empty set (0.00 sec)

mysql> select * from tt;
+-----+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | doc |
+-----+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 100 | {"name":"Tom","age":30,"married":false,"address":{"houseNumber":1001,"streetName":"main","zipcode":"98761","state":"CA"},"cars":["F150","Honda"],"memo":null} |
+-----+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

I can not find data using document path.

What should I do?

[tianx]

Hi @joanney1,

Thanks for using the document feature!

I tried your test case but couldn't reproduce the problem you had. The query works fine for me. Please feel free to let us know if you have additional questions.

mysql> select id, doc.name from tt where doc.address.zipcode like '98761';
+-----+--------------+
| id  | `doc`.`name` |
+-----+--------------+
| 100 | Tom          |
+-----+--------------+
1 row in set (0.00 sec)

mysql> select * from tt;
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id  | doc                                                                                                                                                           |
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 100 | {"name":"Tom","age":30,"married":false,"address":{"houseNumber":1001,"streetName":"main","zipcode":"98761","state":"CA"},"cars":["F150","Honda"],"memo":null} |
+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select doc.name from tt;
+--------------+
| `doc`.`name` |
+--------------+
| Tom          |
+--------------+
1 row in set (0.00 sec)

mysql> select doc.address.zipcode from tt;
+---------------------------+
| `doc`.`address`.`zipcode` |
+---------------------------+
| 98761                     |
+---------------------------+
1 row in set (0.00 sec)

[joanney1]

My Test is very simple

Download current zip file.
unzip source file.
cmake without options
make
make install
./scripts/mysql_install_db
./bin/mysqld_safe --user=mysql

Connecting mysql client tool.

mysql> show variables like '%doc%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| allow_document_type | ON |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table t( id int not null, doc document not null, primary key (id), unique key id_doc (id, doc.address.zipcode as int))engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values ( 1, '{"name":"tom","age":30}');
Query OK, 1 row affected (0.00 sec)

mysql> select doc.name from t;
+--------------+
| doc.name |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)

Did I compile something wrong?
Did I install mysql something wrong?
And
Did I change system variables's value something wrong?

I don't know what I did something wrong.

[tianx]

Hi @joanney1,

Sorry for the delay in replying. I am not clear why you hit this problem. I tried the same queries and still couldn't reproduce - all queries work for me.

We have many more test cases using document paths including some queries similar to yours, covered in our test file: http://github.com/facebook/mysql-5.6/blob/webscalesql-5.6.24.97/mysql-test/suite/json/t/type_document_path.test. Please take a look at the test file and try to run our test cases to see they work for you.