HomeBig DataDiscover and Exchange Textual content with SQL Common Expressions in Rockset

Discover and Exchange Textual content with SQL Common Expressions in Rockset


In our first weblog, we used a daily expression to exchange the quotes in genres. Afterward, we have been in a position to UNNEST() the JSON object. We’ll be working with the identical information set on this weblog

In our information:

Embedded content material: https://gist.github.com/nfarah86/ef1cc9da88e56226c4c46fd0e3c8e16e

there’s a JSON string that’s referred to as spoken_languages, and it’s formatted equally to genres:

[ { "spoken_languages": "[{'iso_639_1': 'fr', 'name': 'Français'}]" }]

Assuming all the pieces is constant, we are able to simply write the SQL assertion much like what we wrote for genres— proper?


Find and replace text using regular expression in SQL - drums

Incorrect ⛔️

We really get a parsing error:
json parse error on line 0 close to `x9akai”}, {“iso_’: unknown escape 120 in string

So, we kind of know what the wrongdoer is right here, however we don’t know if there are going to be extra parsing errors. Let’s go forward and debug this, so we are able to get this SQL assertion working!

We are able to really use REGEXP_LIKE() to see what precisely is inflicting the error:

SELECT
    spoken_languages
FROM
    commons.TwtichMovies t
WHERE REGEXP_LIKE(t.spoken_languages,'x9akai')

This can be a pattern of what we get again:


Find and replace text using regular expression in SQL - sample query

That is nice— it seems just like the are literally inflicting some points. We are able to use REGEXP_REPLACE() to exchange these slashes with an empty string:

SELECT (REGEXP_REPLACE('Lietuvix9akai', '',''));

Now— to place all of it collectively: How will we UNNEST() spoken_languages and repair the two points at hand (the string format and the double slashes)?

Trace:

 SELCT REGEXP_REPLACE(REGEXP_REPLACE('[{''iso_639_1'': ''lt'', ''name'':''Lietuvix9akai''}]', '''', '"'), '', '');

I’m certain you’ll be able to take it to the end line from right here, however simply in case, you’ll be able to watch this youtube hyperlink down under to catch the complete replay!

Embedded content material: https://youtu.be/8aHgJrQjT4U

TLDR: yow will discover all of the sources you want within the developer nook.



RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments