--- title: "Manage records" description: "Guide to managing Aerospike records using AQL commands like INSERT, DELETE, SELECT, and AGGREGATE." --- # Manage records > For the complete documentation index see: [llms.txt](https://aerospike.com/docs/llms.txt) > > All documentation pages available in markdown. This page describes Aerospike Quick Look (AQL) commands to manage records in a database. ## INSERT INTO To insert a record, use the following command: ```plaintext INSERT INTO [.] (PK, ) VALUES (, ) ``` The command contains the following arguments: - `` : Namespace for the record. - `` : Name of the set for the record. - `` : Record’s primary key. - `` : Comma-separated list of bin names. - `` : Comma-separated list of bin values, which may include type cast expressions. Set to `NULL` to delete the bin. Example: Insert a record ```text INSERT INTO test.testset (PK, a, b) VALUES ('xyz', 'abc', 123) ``` ## DELETE FROM To delete a record, use the following command: ```plaintext DELETE FROM [.] WHERE PK= ``` The command contains the following arguments: - `` : Namespace for the record. - `` : Name of the set for the record. - `` : Record’s primary key. Example: Delete a record ```text DELETE FROM test.testset WHERE PK='xyz' ``` ## EXPLAIN SELECT To look at the details of a specific record, use the following command. You must use the primary key of the record: Example: View specific records ```text EXPLAIN SELECT * FROM namespaceName.setName WHERE PK=valueOfPrimaryKey ``` Results for the `valueOfPrimaryKey` object are displayed as a table by default. To display results in JSON format, you must set the output mode: ```plaintext SET OUTPUT JSON OUTPUT = JSON ``` For other allowable values, enter `HELP SET`. You can also specify `outputmode = 'outputType'` in your configuration file. ## SELECT \* FROM To query all records from a specific namespace and set, use the following primary index (PI) query: ```plaintext SELECT * FROM [.] ``` The command contains the following arguments: - `` : Namespace. - `` : Name of the set. Example: Output for a query of all records in a set ```text SELECT * FROM users.profiles +---------------------------+-----+--------+ | name | age | gender | +---------------------------+-----+--------+ | "Bob White" | 22 | "M" | | "Annie Black" | 28 | "F" | | "Sally Green" | 19 | "F" | | "Ricky Brown" | 20 | "M" | | "Tammy Argent" | 22 | "F" | +---------------------------+-----+--------+ 5 rows in set (0.000 secs) ``` ::: note Starting with Tools 3.8.2, `SELECT *` prints the primary key of a record in a column ‘PK’ if it was sent to the server when the record was written. ::: ## SELECT < bin > To query project-specific bins using a primary index query against all records in a specific namespace and set, enter the following: ```plaintext SELECT [, [, ...]] FROM [.] ``` The command contains the following arguments: - `` : Namespace. - `` : Name of the set. - `` : Indicates whether one or more bins are returned from the records. Example: Query project-specific bins ```text SELECT name, age FROM users.profiles +---------------------------+-----+ | name | age | +---------------------------+-----+ | "Bob White" | 22 | | "Annie Black" | 28 | | "Sally Green" | 19 | | "Ricky Brown" | 20 | | "Tammy Argent" | 22 | +---------------------------+-----+ 5 rows in set (0.000 secs) ``` ## SELECT < bin > FROM < ns > To filter all records in a specific namespace and specific set based on a predicate, enter the following command: ```plaintext SELECT [, [, ...]] FROM [.] [IN indextype] [WHERE ] [AND ] [LIMIT ] ``` To reduce the number of records returned, use the `LIMIT ` specifier. Use this specifier when you want to return an entire namespace or set. For example: ```plaintext SELECT * from users.profiles LIMIT 100 ``` The index type specifier `IN indextype` is required in case the secondary index is on [LIST](https://aerospike.com/docs/develop/data-types/collections/list), [MAPKEYS](https://aerospike.com/docs/develop/data-types/collections/map), or [MAPVALUES](https://aerospike.com/docs/develop/data-types/collections/map). If not specified, the specifier defaults to a basic index on the bin itself. When filtering records, consider the following: - The argument `` must be one of the supported predicates for the secondary index. - For NUMERIC indexes, either a range predicate or equality predicate can be applied. - For STRING indexes, only equality predicates are supported. - When using a double where clause both predicates, `` and ``, must be equality operations, and one of the bins in either predicate must have a sindex defined. If `age` has a NUMERIC secondary index defined, this is a valid `SELECT` command: ```plaintext SELECT name, age, city FROM users.profiles WHERE age = 20 and city = "San Francisco" ``` Enter a predicate in the following forms, depending on the datatype: - For Numeric and String, this states that the bin < bin > must be equal to < value > : ` = `. - For Numeric, this states that the bin < bin > must fall within the range between < lower > and < upper > (inclusive): ` BETWEEN AND `. - For GeoJSON, this returns value containing specific location points: ` CONTAINS `. - For GeoJSON, this returns values within a specified range of points: ` WITHIN `. The following is an example of a GeoJSON range SI query: Example: GeoJSON range SI query ```text SELECT * FROM test.demo WHERE gj CONTAINS CAST('{"type": "Point", "coordinates": [0.0, 0.0]}' AS GEOJSON) ``` An equality predicate is in the form which states that the bin `` must equal `` : ```plaintext = ``` To retrieve a record for a specific primary key: ```plaintext PK = ``` In this example, there is a secondary index on the age bin, and there is an SI query to identify all people between the age of 20 and 29: ```plaintext SELECT name, age FROM users.profiles WHERE age BETWEEN 20 AND 29 +---------------------------+-----+ | name | age | +---------------------------+-----+ | "Bob White" | 22 | | "Annie Black" | 28 | | "Ricky Brown" | 20 | | "Tammy Argent" | 22 | +---------------------------+-----+ 4 rows in set (0.000 secs) ``` This SI query assumes a secondary index was created using the following command: ```plaintext CREATE INDEX user_age_idx ON users.profiles (age) NUMERIC ``` ## AGGREGATE To perform an aggregation on SI query results, enter the following command: ```plaintext AGGREGATE .([[,...]]) ON [.] [IN indextype] WHERE ``` Remove `WHERE ` to run aggregation on Primary Index (PI) query results: ```plaintext AGGREGATE .([[,...]]) ON [.] ``` This query specifies a Stream UDF to execute against the results of an SI query. The command contains the following arguments: - `` is the UDF module name. - `` is the Stream UDF function name. - `` are the arguments for the UDF function. Multiple arguments can be specified. - `` is the same predicate used for querying. In the following example, the SI query has an aggregation stream applied to it called `avg_age`. This has the appropriate sub-functions required to define the different stages of the stream (function `female`, `name_age`, and `eldest`). This set of functions are included in the UDF module named `profile_aggregator` : Example: SI query with aggregation stream ```text AGGREGATE profile_aggregator.avg_age() ON users.profiles WHERE age BETWEEN 20 and 29 +--------------------------------------+ + avg_age | +--------------------------------------+ + { "name": "Annie Black", "age": 28 } | +--------------------------------------+ ``` This is the output of a registered UDF module named `profile_aggregator` and which contains a `avg_age` function: ```lua function avg_age(stream) local function female(rec) return rec.gender == "F" end local function name_age(rec) return map{ name=rec.name, age=rec.age } end local function eldest(p1, p2) if p1.age > p2.age then return p1 else return p2 end end return stream : filter(female) : map(name_age) : reduce(eldest) end ``` The stream operations performs the following: - Filters female users. - Converts each profile record into a map containing only a name and age. - Reduces each profile to eldest female. ## Get a record using DIGEST When providing the HEX representation of the digest (for example from the server logs), use `DIGEST` : ```plaintext SELECT * FROM [.] WHERE DIGEST='' ``` The `DIGEST` command provides the HEX representation of the digest (for example from the server logs). When providing the Base64 representation of the digest (for example from asbackup file), use `EDIGEST` : ```plaintext SELECT * FROM [.] WHERE EDIGEST='' ``` The command contains the following arguments: - `` is the namespace for the record. - `` : Name of the set for the record. - `` is the hexadecimal representation of the record’s digest. - `` is the Base64 representation of the record’s digest. Example: Get a record using DIGEST ```text SELECT * FROM test.testset where DIGEST='139FE89822B63DFC173AEA51CCF2EF091AB3129F' +---------+---------+-----------------------------------+---------------+-------+ | bin1 | bin2 | bin3 | LDTCONTROLBIN | binl1 | +---------+---------+-----------------------------------+---------------+-------+ | "val01" | "val02" | ["string1", "string2", "string3"] | | | +---------+---------+-----------------------------------+---------------+-------+ 1 row in set (0.000 secs) ``` ```plaintext SELECT * FROM test.testset where EDIGEST='E5/omCK2PfwXOupRzPLvCRqzEp8=' +---------+---------+-----------------------------------+---------------+-------+ | bin1 | bin2 | bin3 | LDTCONTROLBIN | binl1 | +---------+---------+-----------------------------------+---------------+-------+ | "val01" | "val02" | ["string1", "string2", "string3"] | | | +---------+---------+-----------------------------------+---------------+-------+ 1 row in set (0.000 secs) ``` ## Define datatype You must specify the datatype of a bin value when you create the record. Possible options are MAP, LIST, GeoJSON, and so on. Once you’ve created a record, you can begin to use record operations such as INSERT, DELETE, or SELECT. You can specify the data type in two ways: 1. Define the datatype directly: ```plaintext INSERT INTO test.testset(PK, a,b) VALUES ('xyz9', 'abc10', MAP('{"map":1, "of":2, "items":3}')) INSERT INTO test.demo (PK, gj) VALUES ('key1', GEOJSON('{"type": "Point", "coordinates": [123.4, -456.7]}')) ``` 2. Use the `CAST` expression to define the datatype: ```plaintext INSERT INTO test.testset (PK, a, b) VALUES ('xyz11', 'abc11', CAST('{"map":100, "of":200, "items":300}' AS MAP)) INSERT INTO test.testset(PK, a,b) VALUES ('xyz12', 'abc12', CAST('{"type": "Point", "coordinates": [123.4, -456.7]}' as GEOJSON)) ```