This tag is intended for questions dealing with accessing and reading data elements or series in JSON objects and/or representing such data elements or series with transformations.
Questions tagged [json-extract]
126 questions
1
vote
2 answers
Convert json column content key value pairs to separated key value
My table looks like this:
id
data
1
{tags: {"env": "dev", "owner": "me"}}
I want to fetch the data and inside the select query convert the data column to the following format:
id
data
1
{tags: [{"key": "env", "value": "dev"},…
BenLa
- 27
- 1
- 6
1
vote
1 answer
json_extract_scalar is not extracting value from json string with single quote
I have an athena record with a json string as below.
{'is_referred': False, 'landing_page': '/account/register'}
I tried to extract the landing_page value with query below but it returns null value.
select
json_string,
…
Lee
- 2,874
- 3
- 27
- 51
1
vote
3 answers
JSON_Extract from list of json string
I want to extract some values for particular keys from a table with json string as below.
raw_data
...
{"label": "XXX", "lines":[{"amount":1000, "category": "A"}, {"amount":100, "category": "B"}, {"amount":10, "category": "C"}]}
...
I am…
deepAgrawal
- 673
- 1
- 7
- 25
1
vote
2 answers
json_extract mysql doesn't work as intended when comparing string and int types
I am trying to extract the value of a key in a json and it isn't being recognized properly :
This is what I expected :
mysql> SET @json_ = '{"year":"2022"}';
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT JSON_EXTRACT(@json_,…
Aravinth Balakrishnan
- 96
- 1
- 7
1
vote
1 answer
how to extract json keys and values as table with 2 columns mysql 5.7
Having this in column named "value" on table named "test" with varchar data type:
'{"3": "3", "2": "7", "6": "1", "1": "2", "5": "5"}'
the output I need:
col1 col2
3 3
2 7
6 1
1 2
5 5
I'm having difficulties to…
Daniya Cranberry
- 13
- 3
1
vote
2 answers
Extract the count of session with null values with condition of platform name
I want to extract the COUNT OF session which are of null values from the below json. I tried with online json path extractor its work but the tried on java code with JsonPath lib, its showing parameter is not defind.
*{
"value": {
"ready":…
SacTan
- 379
- 2
- 5
- 18
1
vote
1 answer
malformed JSON Error in Sqlite when i use json_extract funciton
In my sqllite table i have field named config which stores configuration in the form of json text..
bellow is the sample data stored in config field
{
"matRid": 1,
"holderType": 1,
"uomRid": 1,
"type": 502,
"stockConditionIndex":…
Mahesh Gouda
- 118
- 9
1
vote
1 answer
SQLite3 JSON1 Order by numeric index
I have a table like this:
TestTable
---------
data (TEXT)
All data values are JSON objects like { a:1, b:2, c:3 }.
I want to be able to query the database and ORDER BY data->b DESC without a full table scan (indexed).
Is this possible in SQLite…
Lucien
- 776
- 3
- 12
- 40
1
vote
0 answers
How to extract array values from mysql json field and return null or default if path does not exist?
I have this json in a mysql column.
{"items": [
{
"name": "a1",
"details": {"value": 1}
},
{
"name": "a2",
"details": {"value": 2}
},
{
"name": "a3"
},
{
"name":…
1
vote
3 answers
How to use json_extract in standard sql to get a value
If the column has value like the below
[{"name": "my_name_1"}] [{"name": "my_name_2"}] [{"name": "my_name_3"}] [{"name": "my_name_4"}]
How do I only get my_name_1 using json_extract?
I tried the following but didnt work…
selvinkb
- 11
- 1
1
vote
1 answer
SQLite JSON_EXTRACT All values of 1 object in an array
I have a column containing JSON arrays. The arrays are like this (table users, column user_info):
[
{"email":"xyz@hotmail.com","user_key":"987654","name":"John Paul"},
{"email":"abc@hotmail.com","user_key":"123456","name":"Tom Sawyer"},
…
SnakeDoc65
- 91
- 7
1
vote
0 answers
MariaDB-JSON-document- Not able to select only specific elements from array without knowing his index after applying exact where clause
I have JSON document. structure/sample data is like -
{
"id":"201",
"portfolio":[
{
"portfolio_id":"PORTFOLIO_001",
"portfolio_name":"AAA",
"product":[
{
…
Prashant Vidhate
- 11
- 1
1
vote
1 answer
Mysql Json Extract - wild card selection of key
Below is the json blob that I have in my database
{
RootData: {
202003: {
201903: {
"abc": 123,
xyz: 456
},
data1: {
},
data2: {
}
}
}
}
Right now I have a query…
Suresh Atta
- 120,458
- 37
- 198
- 307
1
vote
2 answers
Extracting JSON value from a MySQL query
I have data which is of type json in mysql and column name is 'student_data'. student_data:
{
"STUDENT_HISTORY": [
{
"regno": "12345678",
"UPDATE_DATE_UNIX_TIME": "65667"
},
{
"regno": "12345332",
…
Neela Kandan
- 11
- 2
1
vote
1 answer
How to extract nested JSON Object in Hive
I have a column named "impact" which has data in nested Json…
Stella
- 69
- 1
- 10