Is there a way to query tree structure json data in sqlite?

[sukechris]

Thank you for your great work! I encountered a problem, I have a field in my sqlite table, and this field is tree structure json data, like this：

[
  {
    id: '100',
    title: 'Frontend',
    children: [
      {
        id: '110',
        title: 'React',
        children: [
          {
            id: '111',
            title: 'React Hooks',
          },
          {
            id: '112',
            title: 'React Router',
          },
        ],
      },
      {
        id: '120',
        title: 'Vue',
        children: [
          {
            id: '121',
            title: 'Vue Router',
          },
          {
            id: '122',
            title: 'Vuex',
          },
        ],
      },
    ],
  },
  {
    id: '200',
    title: 'Backend',
    children: [
      {
        id: '210',
        title: 'Java',
      },
      {
        id: '220',
        title: 'Rust',
      },
    ],
  },
]

I want to query title value by keyword, Is there a good way to achieve it?

[billy1624]

Hey @sukechris, welcome! You got this sorted?

[sukechris]

@billy1624 I don't know how to solve it with sea-orm for now，but I realized this issue may not be related to sea-orm，It's possible to solve it by native sqlite query with json_each or json_tree，and I'll try it later

[billy1624]

Got it! Thanks for the updates :P

[boan-anbo]

Thanks for the tips, I was able to use the following:

 let db = self.connect().await.unwrap();
 let files = MyTable::find()
      .from_raw_sql(
          Statement::from_sql_and_values(
              DbBackend::Sqlite,
              r#"SELECT * FROM mytable WHERE json_extract(my_table.json_field, $1) = $2"#,
              vec![json_field_key.into(), json_field_value.into()],
          )
      )
      .all(&db)
      .await.map_err(OrmError::DatabaseQueryError)?;
  Ok(files)