PartiQL Query to return items in a list of objects that contains certain string

Question

I want to retrieve only the short_name that contains the string "BR", I've tried using PartiQL docs but some features are not allowed in DynamoDB. The reason is that right now we are using DynamoDB for Geocache wrong and now I need to filter for a specific country.

SELECT googleResult.address_components FROM Geocache

This is the current return for this query:

"address_components": {
                    "L": [
                        {
                            "M": {
                                "long_name": {
                                    "S": "803"
                                },
                                "short_name": {
                                    "S": "803"
                                },
                                "types": {
                                    "L": [
                                        {
                                            "S": "street_number"
                                        }
                                    ]
                                }
                            }
                        },
                        {
                            "M": {
                                "long_name": {
                                    "S": "Rua Sócrates"
                                },
                                "short_name": {
                                    "S": "R. Sócrates"
                                },
                                "types": {
                                    "L": [
                                        {
                                            "S": "route"
                                        }
                                    ]
                                }
                            }
                        },
                        {
                            "M": {
                                "long_name": {
                                    "S": "Jardim Marajoara"
                                },
                                "short_name": {
                                    "S": "Jardim Marajoara"
                                },
                                "types": {
                                    "L": [
                                        {
                                            "S": "political"
                                        },
                                        {
                                            "S": "sublocality"
                                        },
                                        {
                                            "S": "sublocality_level_1"
                                        }
                                    ]
                                }
                            }
                        },
                        {
                            "M": {
                                "long_name": {
                                    "S": "São Paulo"
                                },
                                "short_name": {
                                    "S": "São Paulo"
                                },
                                "types": {
                                    "L": [
                                        {
                                            "S": "administrative_area_level_2"
                                        },
                                        {
                                            "S": "political"
                                        }
                                    ]
                                }
                            }
                        },
                        {
                            "M": {
                                "long_name": {
                                    "S": "São Paulo"
                                },
                                "short_name": {
                                    "S": "SP"
                                },
                                "types": {
                                    "L": [
                                        {
                                            "S": "administrative_area_level_1"
                                        },
                                        {
                                            "S": "political"
                                        }
                                    ]
                                }
                            }
                        },
                        {
                            "M": {
                                "long_name": {
                                    "S": "Brasil"
                                },
                                "short_name": {
                                    "S": "BR"
                                },
                                "types": {
                                    "L": [
                                        {
                                            "S": "country"
                                        },
                                        {
                                            "S": "political"
                                        }
                                    ]
                                }
                            }
                        },
                        {
                            "M": {
                                "long_name": {
                                    "S": "04671-205"
                                },
                                "short_name": {
                                    "S": "04671-205"
                                },
                                "types": {
                                    "L": [
                                        {
                                            "S": "postal_code"
                                        }
                                    ]
                                }
                            }
                        }
                    ]
                }

score 1 · Accepted Answer · answered Apr 13 '23 at 12:47

You cannot do that in DynamoDB, as you have the data stored in a List and you are not able to search every component in a list for a given value. Why not store the values as a Map with the short_name code as its key?

"address_components": {
  "M": {
   "803": {
    "M": {
     "long_name": {
      "S": "803"
     },
     "short_name": {
      "S": "803"
     },
     "types": {
      "L": [
       {
        "S": "street_number"
       }
      ]
     }
    }
   },
   "BR": {
    "M": {
     "long_name": {
      "S": "Brasil"
     },
     "short_name": {
      "S": "BR"
     },
     "types": {
      "L": [
       {
        "S": "country"
       },
       {
        "S": "political"
       }
      ]
     }
    }
   }
  }
 }

Now you can do a Scan for all items which have a short_name of "BR":

aws dynamodb scan \
--table-name tableName \
--filter-expression 'attribute_exists(#v.#x)' \
--expression-attribute-names '{"#v": "address_components", "#x": "BR"}'

PartiQL Query to return items in a list of objects that contains certain string

1 Answers1