split character string in cell with sql
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-15-2022 08:44 PM
I have the following input:
I am looking for a way to split the characters in the item_order_detail column into 2 columns itemID and itemName. As below output table uses SQL function in databricks with spark_sql version 3.2.1.
Can someone suggest a solution for me?
Thanks all
- Labels:
-
Databricks SQL
-
SQL
-
Sql query
-
String
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-15-2022 10:11 PM
Hi, you got to extract itemID and itemName from single column item_order_detail - you can make use of from_json function for that. I hope following code helps,
SELECT order_id, from_json(item_order_details, 'itemid STRING, itemname STRING') as items
FROM orders
SELECT order_id, items.itemid, items.itemname
FROM orders
LATERAL VIEW from_json(item_order_details, 'itemid STRING, itemname STRING') items as items
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-15-2022 10:42 PM
and pyspark for the same-
df = spark.table("orders")
df = df.withColumn("items", from_json(df["item_order_details"], "itemid STRING, itemname STRING"))
df = df.select("order_id", "items.itemid", "items.itemname")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-16-2022 08:02 AM
Hi @Wasim Inamdar
I tried it and have error bellow:
SELECT order_id, from_json(item_order_details, 'itemid STRING, itemname STRING') as items
FROM orders
result
and
SELECT order_id, items.itemid, items.itemname
FROM orders
LATERAL VIEW from_json(item_order_details, 'itemid STRING, itemname STRING') items as items
Error in SQL statement: AnalysisException: from_json is expected to be a generator. However, its class is org.apache.spark.sql.catalyst.expressions.JsonToStructs, which is not a generator
![](/skins/images/1C7D039E274DA4E433FB1B1A3EAE173A/responsive_peak/images/icon_anonymous_profile.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-16-2022 12:53 PM
If you have a struct type and not json, you can explode the column and then extract the parts. https://spark.apache.org/docs/latest/api/sql/index.html#explode will unpack or flatmap the array. Just select order and explode("item_order_details")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-05-2023 09:49 PM
you need to use explode function
https://stackoverflow.com/questions/61070630/spark-explode-column-with-json-array-to-rows
![](/skins/images/B38AF44D4BD6CE643D2A527BE673CCF6/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/B38AF44D4BD6CE643D2A527BE673CCF6/responsive_peak/images/icon_anonymous_message.png)