# Custom sql - Select all key-value data from a field with string data type

**URL:** <https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201>\
**Category:** Q&A\
**Tags:** filters, data-source, Business-Intelligence-Engineer, quick-sight\
**Created:** [September 17, 2024, 7:35am UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201 "2024-09-17T07:35:14Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![emyatsuna](https://avatars.discourse-cdn.com/v4/letter/e/c2a13f/32.png) [@emyatsuna](https://community.amazonquick.com/u/emyatsuna)\
**Post date:** [September 17, 2024, 7:35am UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/1 "2024-09-17T07:35:14Z")

</div>

Hi, I have a sample test string value below. The string is patterned like an array of hashes or dictionaries. I want to get the name values with type type02.

Expected output: id = “test03, test04, test05”

Sample test string:

```auto
data = "[{'testdata': 'has', 'name': 'test01', 'type': 'type01'}, {'testdata': 'does not have', 'name': 'test02', 'type': 'type03'}, {'testdata': 'has', 'id': 'test03', 'type': 'type02'}, {'testdata': 'has', 'id': 'test04', 'type': 'type02'}, {'testdata': 'has', 'id': 'test05', 'type': 'type02'}]"

```

At first I used calculated field but I can only retrieve the first type02 info in the string.

```auto
ifelse(
    contains(split(data, ',' , 2),'type02'),
    replace(replace(split(data, ',' , 2), "'id':", ''), "'", ''),
    NULL
)

```

output: id = “test03”

Since the value datatype is a string and there are a few ids that I need, I am trying to use custom SQL and create a new table field.  
In Athena, I trimmed the data value then tried to use json\_extract\_scalar() to get the key-value data inside but was unsuccessful.

```auto
trim('[]' from data) as trimmed_data
json_extract_scalar(replace(replace(trimmed_data, '''', '"'), ': False', ': false'),'$.id') AS id

```

Do you have any suggestions on how to parse this type of data? Thank you.

---

<div class="post-metadata">

**Author:** ![andres007](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/andres007/32/8734_2.png) [@andres007](https://community.amazonquick.com/u/andres007)\
**Post date:** [September 17, 2024, 7:55am UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/2 "2024-09-17T07:55:41Z")

</div>

Hi,

Question, Is the number of items in the data dictionary fixed or can it vary per row?

---

<div class="post-metadata">

**Author:** ![emyatsuna](https://avatars.discourse-cdn.com/v4/letter/e/c2a13f/32.png) [@emyatsuna](https://community.amazonquick.com/u/emyatsuna)\
**Post date:** [September 17, 2024, 7:57am UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/3 "2024-09-17T07:57:59Z")

</div>

Hello @andres007 , thank you for checking. The number of items are not fixed. And yes, it varies per row. Sometimes some keys like the id or type is not existing. Thanks

---

<div class="post-metadata">

**Author:** ![andres007](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/andres007/32/8734_2.png) [@andres007](https://community.amazonquick.com/u/andres007)\
**Post date:** [September 17, 2024, 9:43am UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/4 "2024-09-17T09:43:59Z")

</div>

Hi,

If the number of elems and items is not known I have no ideas to try out, maybe someone else can help you here. If they were fixed you could just try nesting ifelse with splits.

---

<div class="post-metadata">

**Author:** ![emyatsuna](https://avatars.discourse-cdn.com/v4/letter/e/c2a13f/32.png) [@emyatsuna](https://community.amazonquick.com/u/emyatsuna)\
**Post date:** [September 17, 2024, 11:39am UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/5 "2024-09-17T11:39:08Z")

</div>

thanks for your help, @andres007 !

---

<div class="post-metadata">

**Author:** ![emyatsuna](https://avatars.discourse-cdn.com/v4/letter/e/c2a13f/32.png) [@emyatsuna](https://community.amazonquick.com/u/emyatsuna)\
**Post date:** [October 1, 2024, 6:16am UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/6 "2024-10-01T06:16:29Z")

</div>

I’m still getting the same issue. Thank you.

---

<div class="post-metadata">

**Author:** ![Brett](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brett/32/36494_2.png) [@Brett](https://community.amazonquick.com/u/Brett)\
**Post date:** [October 28, 2024, 4:09pm UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/7 "2024-10-28T16:09:48Z")

</div>

Hi @emyatsuna,  
It’s been awhile since last communication in this thread, did you have any additional questions or were you able to find a work around for your case?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

---

<div class="post-metadata">

**Author:** ![Brett](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brett/32/36494_2.png) [@Brett](https://community.amazonquick.com/u/Brett)\
**Post date:** [October 31, 2024, 3:49pm UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/8 "2024-10-31T15:49:20Z")

</div>

Hi @emyatsuna,  
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!

---

<div class="post-metadata">

**Author:** ![Brett](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brett/32/36494_2.png) [@Brett](https://community.amazonquick.com/u/Brett)\
**Post date:** [October 31, 2024, 3:49pm UTC](https://community.amazonquick.com/t/custom-sql-select-all-key-value-data-from-a-field-with-string-data-type/35201/9 "2024-10-31T15:49:43Z")

</div>

Archived on October 31, 2024, 3:49pm UTC


