# Extracting specific fields from the MMDB database **URL:** \ **Category:** Database Downloads\ **Tags:** mmdb, mmdbctl\ **Created:** [August 23, 2023, 7:54am UTC](https://community.ipinfo.io/t/extracting-specific-fields-from-the-mmdb-database/2395 "2023-08-23T07:54:02Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![Abdullah](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/abdullah/32/4973_2.png) [@Abdullah](https://community.ipinfo.io/u/Abdullah)\ **Post date:** [August 23, 2023, 7:54am UTC](https://community.ipinfo.io/t/extracting-specific-fields-from-the-mmdb-database/2395/1 "2023-08-23T07:54:02Z")
# MMDB binary file format allows for efficient lookup, but it doesn’t provide the most versatility If you are using an MMDB reader library like: - [C](https://github.com/maxmind/libmaxminddb) - [C#](https://github.com/maxmind/MaxMind-DB-Reader-dotnet) - [Java](https://github.com/maxmind/MaxMind-DB-Reader-java) - [Perl](https://github.com/maxmind/MaxMind-DB-Reader-perl) - [PHP](https://github.com/maxmind/MaxMind-DB-Reader-php) - [Python](https://github.com/maxmind/MaxMind-DB-Reader-python) - [Ruby](https://github.com/maxmind/MaxMind-DB-Reader-ruby) - [Nodejs](https://github.com/runk/node-maxmind) You can extract the specific fields by extracting the specific fields from the response object quite easily. However, if you are using the [`mmdbct`l](https://github.com/ipinfo/mmdbctl) tool, you might want to complement that tool with: - [JQ](https://jqlang.github.io/jq/) for JSON - [Miller](https://miller.readthedocs.io/en/6.8.0/) or the `cut` command or `awk` programming language for the CSV file ## Extracting specific fields from the JSON output For this post, we are going to use our [IP to Geolocation database](https://ipinfo.io/products/ip-geolocation-database) and extract the following fields from the database: - `city` - `country` - `region` - `postal_code` - `latitude` - `longitude` Single input IP address: `128.143.220.179` Querying the IP location using the MMDBctl tool of a single IP address: ```bash mmdbctl read -f json-pretty ipinfo_location.mmdb ``` Response: ```json { "city": "Charlottesville", "country": "US", "latitude": "38.02931", "longitude": "-78.47668", "postal_code": "22902", "region": "Virginia", "timezone": "America/New_York" } ``` We can extract the specific fields using [jq](https://jqlang.github.io/jq/). In that case, we have to declare the fields we want to extract in the object template declaration. ```bash mmdbctl read -f json-pretty 128.143.220.179 ipinfo_location.mmdb | jq '{"city": .city, "country": .country, "region": .region, "postal_code": .postal_code, "latitude": .latitude, "longitude": .longitude}' ``` Response: ```json { "city": "Charlottesville", "country": "US", "region": "Virginia", "postal_code": "22902", "latitude": "38.02931", "longitude": "-78.47668" } ``` Bulk IP address lookup: `128.143.220.179/30` The above code using JQ works precisely the same, even with bulk IP address lookup: ```bash mmdbctl read -f json-pretty 128.143.220.179/30 ipinfo_location.mmdb | jq '{"city": .city, "country": .country, "region": .region, "postal_code": .postal_code, "latitude": .latitude, "longitude": .longitude}' ``` Response: ```json { "city": "Charlottesville", "country": "US", "region": "Virginia", "postal_code": "22902", "latitude": "38.02931", "longitude": "-78.47668" } { "city": "Charlottesville", "country": "US", "region": "Virginia", "postal_code": "22902", "latitude": "38.02931", "longitude": "-78.47668" } { "city": "Charlottesville", "country": "US", "region": "Virginia", "postal_code": "22902", "latitude": "38.02931", "longitude": "-78.47668" } { "city": "Charlottesville", "country": "US", "region": "Virginia", "postal_code": "22902", "latitude": "38.02931", "longitude": "-78.47668" } ``` Bulk IP address lookup: `133.254.182.207 197.38.153.13` ```bash mmdbctl read -f json-pretty 133.254.182.207 197.38.153.13 ipinfo_location.mmdb | jq '{"city": .city, "country": .country, "region": .region, "postal_code": .postal_code, "latitude": .latitude, "longitude": .longitude}' ``` Response ```json { "city": "Tokyo", "country": "JP", "region": "Tokyo", "postal_code": "101-0047", "latitude": "35.691", "longitude": "139.7679" } { "city": "Girga", "country": "EG", "region": "Sohag", "postal_code": "", "latitude": "26.33721", "longitude": "31.89295" } ``` ## Extracting specific fields from the CSV output Single IP address lookup: `128.143.220.179` You can use the AWK programming language to extract the columns: ```bash mmdbctl read -f csv 128.143.220.179/30 ipinfo_location.mmdb | awk -F',' -v OFS=, '{print $2,$3,$4,$5,$6}' ``` The `$2,$3,$4,....` refers to column number: - ip → `$1` - city → `$2` - country → `$3` - latitude → `$4` - longitude → `$5` - postal\_code → `$6` - region → `$7` - timezone → `$8` Response: ```csv city,country,latitude,longitude,postal_code Charlottesville,US,38.02931,-78.47668,22902 Charlottesville,US,38.02931,-78.47668,22902 Charlottesville,US,38.02931,-78.47668,22902 Charlottesville,US,38.02931,-78.47668,22902 ``` You can also use the CSV utility [Miller](https://miller.readthedocs.io/en/6.8.0/) which provides a more intuitive way to extract the columns ```bash mmdbctl read -f csv 4.85.5.133 126.233.162.255 ipinfo_location.mmdb | mlr --csv cut -f city,country,latitude,longitude,postal_code ``` ```csv city,country,latitude,longitude,postal_code Monroe,US,32.553,-92.0422,71203 Tokyo,JP,35.6895,139.69171,101-8656 ``` ## Going beyond MMDB… Although the MMDB database provides an efficient way to look up IP addresses, however considering their limited scope and RAM usage, you might want to look into other solutions to enrich IP addresses with IPinfo’s databases. You should definitely check out our community post on: - [Using IPinfo’s database on PostgreSQL](https://community.ipinfo.io/t/using-ipinfo-s-data-downloads-in-postgresql/1000) - [Our Snowflake community docs](https://community.ipinfo.io/docs?tags=snowflake) - [Using IPinfo’s data on Clickhouse](https://community.ipinfo.io/t/lookup-ip-geolocation-and-asn-with-clickhouse-and-ipinfos-free-database/149)