Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Hazelcast sql query with JSON_QUERY and filter

I try to do a SQL query on Hazelcast map.
ata in the map is in Json format.

How can I do a query with json filter ? I need to get all data with hasEvents == true

I tried to do this filter :

select JSON_QUERY(this, '$.data.field1.hasEvents?==true') from products

but it doesn’t work

I expect to get all the entries in the map where data.field1.hasEvents = true

Tags:

2

Answers


  1. 0

    Assume that you have this table structure

    CREATE TABLE IF NOT EXISTS product (id INT NOT NULL, data VARCHAR(100), PRIMARY KEY (id));
INSERT INTO product (id,data) VALUES (1, '{
  "field1": {
    "hasEvents": true
  }
}');

INSERT INTO product (id,data) VALUES (2, '{
  "field1": {
    "hasEvents": false
  }
}');

    Your SQL should be something like

    SELECT * FROM product WHERE JSON_QUERY(data, '$.field1.hasEvents') = 'true'
    Login or Signup to reply.
  2. 0

    I am using hz 5.4.0-SNAPSHOT in this example. Link to the complete example is here
    https://github.com/OrcunColak/hzjetclient/blob/c8b526d0160ab3a2eb576716e314a262898feef4/src/main/java/com/colak/jet/sql/json/json_query/JsonQueryIMapTest.java

    This is the test skeleton

    public static final String MAP_NAME = "product";

public static void main(String[] args) {
  log.info("Starting HZ Server");

  // Start server
  HazelcastInstance hazelcastInstance = getHazelcastServerInstance();
  populateMap(hazelcastInstance );
  createMapping(hazelcastInstance );
  testSelect(hazelcastInstance );
  hazelcastInstance .shutdown();
  log.info("Test completed");
}

    This method creates an embedded hz server

    HazelcastInstance getHazelcastServerInstance() {
  Config config = new Config();

  // Add JetConfig
  JetConfig jetConfig = config.getJetConfig();
  jetConfig.setEnabled(true);
  jetConfig.setResourceUploadEnabled(true);
  return Hazelcast.newHazelcastInstance(config);
}

    Let’s populate the map

    void populateMap(HazelcastInstance hazelcastInstance) {
  IMap<String, HazelcastJsonValue> map = hazelcastInstance .getMap(MAP_NAME);
  map.put("1", new HazelcastJsonValue("""
                {
                  "field1": {
                    "hasEvents": false
                  }
                }
                """));

  map.put("2", new HazelcastJsonValue("""
                {
                  "field1": {
                    "hasEvents": true
                  }
                }
                """));
}

    This one creates a mapping. I have copied from your example

    void createMapping(HazelcastInstance hazelcastInstance) {
  String createMappingQuery = format("CREATE OR REPLACE MAPPING %s Type IMap OPTIONS('keyFormat'='varchar', 'valueFormat'='json')",
                MAP_NAME);

  SqlService sqlService = hazelcastInstance.getSql();
  sqlService.executeUpdate(createMappingQuery);
}

    Now select from the mapping that uses underlying IMap

    void testSelect(HazelcastInstance hazelcastInstance) {
  SqlService sqlService = hazelcastInstance.getSql();
  try (SqlResult sqlResult = sqlService.execute("SELECT __key, this FROM product WHERE JSON_QUERY(this, '$.field1.hasEvents') = 'true'")) {
  if (sqlResult.isRowSet()) {
    String[] columnNames = getColumnNames(sqlResult);
    int numberOfColumns = columnNames.length;
    for (SqlRow sqlRow : sqlResult) {
      for (int columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {
      Object columnValue = sqlRow.getObject(columnIndex);
      log.info("{} : {}", columnNames[columnIndex], columnValue);
      }
     }
   }
 }
}

    I can see from the output that it selects the entry having key = 2

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search