Query Native with LIKE statement. Is it possible?

[GuilhermeVitorino]

Hello,

Are there a way to use LIKE statement in Query Native?

I want do something like that:

@Query(value =
        "DECLARE " +
            "$idDocumento LONG; " +
            "$protocolo STRING; " +
            "$departamento STRING; " +
            "$status STRING; " +
            "$dataSolucao STRING; " +
            "$limit INTEGER; " +
            "$offset INTEGER; " +
        "SELECT * FROM DocumentoEntity AS d " +
        "WHERE " +
            "    ( $idDocumento  = 0  or d.idDocumento           = $idDocumento )" +
            "AND ( $protocolo    = '' or d.kv_json_.protocolo    = $protocolo )" +
            "AND ( $departamento = '' or d.kv_json_.departamento = $departamento )" +
            "AND ( $status       = '' or d.kv_json_.status       = $status )" +
            "AND ( $dataSolucao  = '' or d.kv_json_.dataSolucao  LIKE %$dataSolucao% )" +
        "LIMIT $limit OFFSET $offset", count = true
)
List<DocumentoEntity> findByQuery(
        @Param("$idDocumento") Long idDocumento,
        @Param("$protocolo") String protocolo,
        @Param("$departamento") String departamento,
        @Param("$status") String status,
        @Param("$dataSolucao") String dataSolucao,
        @Param("$limit") int limit,
        @Param("$offset") int offset
);

[cezarfx]

Guilherme, Please take a look at contains string function: https://docs.oracle.com/en/database/other-databases/nosql-database/22.1/sqlreferencefornosql/contains_function.html Cezar

…

________________________________ From: Guilherme Vitorino ***@***.***> Sent: Friday, July 22, 2022 4:45 PM To: oracle/nosql-spring-sdk ***@***.***> Cc: Subscribed ***@***.***> Subject: [External] : [oracle/nosql-spring-sdk] Query Native with LIKE statement. Is it possible? (Issue oracle/nosql-examples#44) Hello, Are there a way to use LIKE statement in Query Native? I want do something like that: ` @query<https://urldefense.com/v3/__https://github.com/query__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ3nCNQNpg$>(value = "DECLARE " + "$idDocumento LONG; " + "$protocolo STRING; " + "$departamento STRING; " + "$status STRING; " + "$dataSolucao STRING; " + "$limit INTEGER; " + "$offset INTEGER; " + "SELECT * FROM DocumentoEntity AS d " + "WHERE " + " ( $idDocumento = 0 or d.idDocumento = $idDocumento )" + "AND ( $protocolo = '' or d.kv_json_.protocolo = $protocolo )" + "AND ( $departamento = '' or d.kv_json_.departamento = $departamento )" + "AND ( $status = '' or d.kv_json_.status = $status )" + "AND ( $dataSolucao = '' or d.kv_json_.dataSolucao LIKE %$dataSolucao% )" + "LIMIT $limit OFFSET $offset", count = true ) List findByQuery( @param<https://urldefense.com/v3/__https://github.com/param__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ2z0gSuqg$>("$idDocumento") Long idDocumento, @param<https://urldefense.com/v3/__https://github.com/param__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ2z0gSuqg$>("$protocolo") String protocolo, @param<https://urldefense.com/v3/__https://github.com/param__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ2z0gSuqg$>("$departamento") String departamento, @param<https://urldefense.com/v3/__https://github.com/param__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ2z0gSuqg$>("$status") String status, @param<https://urldefense.com/v3/__https://github.com/param__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ2z0gSuqg$>("$dataSolucao") String dataSolucao, @param<https://urldefense.com/v3/__https://github.com/param__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ2z0gSuqg$>("$limit") int limit, @param<https://urldefense.com/v3/__https://github.com/param__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ2z0gSuqg$>("$offset") int offset ); ` — Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https://github.com/oracle/nosql-spring-sdk/issues/17__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ3ungejxw$>, or unsubscribe<https://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/AAFE6XRHC5HT5KZIYOWYINDVVMJA3ANCNFSM54M27NYA__;!!ACWV5N9M2RV99hQ!IJ8fryJYGwahVnuER6El09cjYS6raqtJ-bi7l3IMu8Z-FvO6qZZu8s_13Ll3eF6mlT72McNO3nKBiSZfkQ0JkpHbfg$>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[GuilhermeVitorino]

Thank you, Cezar! It worked.

Follow my code:

@Query(value =
        "DECLARE " +
            "$idDocumento LONG; " +
            "$protocolo STRING; " +
            "$canal STRING; " +
            "$departamento STRING; " +
            "$status STRING; " +
            "$dataSolucao STRING; " +
            "$atributosValue STRING; " +
            "$limit INTEGER; " +
            "$offset INTEGER; " +
        "SELECT * FROM DocumentoEntity AS d " +
        "WHERE " +
            "    ( $idDocumento    = 0  or contains(d.kv_json_.idDocumento,  $idDocumento) )"  +
            "AND ( $protocolo      = '' or contains(d.kv_json_.protocolo,    $protocolo) )"    +
            "AND ( $canal          = '' or contains(d.kv_json_.canal,        $canal) )"    +
            "AND ( $departamento   = '' or contains(d.kv_json_.departamento, $departamento) )" +
            "AND ( $status         = '' or contains(d.kv_json_.status,       $status) )"       +
            "AND ( $dataSolucao    = '' or contains(d.kv_json_.dataSolucao,  $dataSolucao) )"  +
            "AND ( $atributosValue = '' or contains(d.kv_json_.atributos[].valor, $atributosValue) )"  +
        "LIMIT $limit OFFSET $offset"
)

[dario-vega]

@GuilhermeVitorino This is a really interesting question for our new Q&A so I moved it to this repository as a GitHub Discussion.
BTW, I am in the process of moving my examples here.
Hope that it was useful for you