How do i query a DynamoDB and get all rows where "col3" exists & not 0/null (boto3)

Question

This is my DynamoDB table via serverless framework, added a secondary column index for "aaa_id":

    Devices:
      Type: AWS::DynamoDB::Table
      Properties:
        TableName: Devices
        BillingMode: PAY_PER_REQUEST
        AttributeDefinitions:
          - AttributeName: serial
            AttributeType: S
          - AttributeName: aaa_id
            AttributeType: N
        KeySchema:
          - AttributeName: serial
            KeyType: HASH
        GlobalSecondaryIndexes:
          - IndexName: aaa_id
            KeySchema:
              - AttributeName: aaa_id
                KeyType: HASH
            Projection:
              ProjectionType: ALL

I want to query my DynamoDB and get all items of the table where the column "aaa_id" exists or isn't 0 (or null, if it's even possible for a Number type column). Some rows don't include it. preferable using the query method instead of scan since i know it's less heavy

I've been on this for hours. please help.

Some of my few fail attempts:

import json
import boto3


def lambda_handler(event, context):
    dynamodb = boto3.resource('dynamodb')
    table = dynamodb.Table('Devices')

    try:
        response = table.query(
            IndexName='aaa_id',
            FilterExpression='aaa_id <> :empty',
            ExpressionAttributeValues={':empty': {'N': '0'}}
        )
        
        items = response['Items']
        return {
            'statusCode': 200,
            'body': json.dumps(items)
        }
        
    except Exception as e:
        print(e)
        return {
            'statusCode': 500,
            'body': json.dumps('Error querying the database')
        }


#################################

import json
import boto3
from boto3.dynamodb.conditions import Key, Attr


def lambda_handler(event, context):

    dynamodb = boto3.resource('dynamodb')
    table = dynamodb.Table('Devices')

    try:
        response = table.query(
            IndexName='aaa_id',
            KeyConditionExpression=Key('aaa_id').gt(0) & Attr('aaa_id').not_exists(),
            ExpressionAttributeValues={
                ':empty': {'N': ''}
            }
        )

        data = response['Items']
        while 'LastEvaluatedKey' in response:
            response = table.query(
                IndexName='aaa_id',
                KeyConditionExpression=Key('aaa_id').gt(0) & Attr('aaa_id').not_exists(),
                ExpressionAttributeValues={
                    ':empty': {'N': ''}
                },
                ExclusiveStartKey=response['LastEvaluatedKey']
            )
            data.extend(response['Items'])

        return {
            'statusCode': 200,
            'body': json.dumps(data),
            'success': True
        }

    except Exception as e:
        return {
            'statusCode': 500,
            'body': json.dumps(str(e)),
            'success': False
        }

#######################

import json
import boto3
from boto3.dynamodb.conditions import Key

def lambda_handler(event, context):

    dynamodb = boto3.resource('dynamodb')
    table = dynamodb.Table('Devices')

    try:
        response = table.query(
            IndexName='aaa_id-index',
            KeyConditionExpression=Key('aaa_id').gt(0)
        )
        
        items = response['Items']
        
        while 'LastEvaluatedKey' in response:
            response = table.query(
                IndexName='aaa_id-index',
                KeyConditionExpression=Key('aaa_id').gt(0),
                ExclusiveStartKey=response['LastEvaluatedKey']
            )
            items.extend(response['Items'])
        
        return {
            'statusCode': 200,
            'body': json.dumps(items),
            'success': True
        }
    
    except Exception as e:
        return {
            'statusCode': 500,
            'body': json.dumps({'error': str(e)}),
            'success': False
        }



##################################



import boto3
import json


def lambda_handler(event, context):

    dynamodb = boto3.client('dynamodb')
        
    
    try:
        response = dynamodb.query(
            TableName="Devices",
            IndexName='aaa_id-index',
            KeyConditionExpression='aaa_id <> :empty',
            # ExpressionAttributeValues={':empty': {'S': ''}}
        )
        
        return {
            'statusCode': 200,
            'body': json.dumps(response['Items']),
            'status': 'success'
        }
        
    except Exception as e:
        return {
            'statusCode': 500,
            'body': json.dumps({'error': str(e)}),
            'status': 'error'
        }

Answer 1

You're over complicating things. Firstly, a number type cannot be null, as DynamoDB is schemaless you simply omit any value.

Secondly, indexes can be sparse. As you're not interested in items which are 0 then simply don't set a value for that, which in turn will mean the item won't exist in the index.

Then you simply Scan the index, as you know that all values that are there are not null and are not 0. In this case Scan is efficient as it's reading exactly what you want.

response = table.scan(
            IndexName='aaa_id'
        )