logo
Tags down

shadow

mariadb sql select distinct values from json array


By : dmfldkckck
Date : September 14 2020, 11:00 PM
this one helps. You can use recursive CTEs and JSON functions to extract the label values.
Here's an example query that extracts the unique values from JSON arrays in a table:
code :
WITH RECURSIVE labels AS (
  SELECT 0 AS depth, JSON_VALUE(data, CONCAT('$[', 0, ']')) AS data
    FROM t1
    WHERE JSON_LENGTH(t1.data) > 0
  UNION
  SELECT l.depth + 1, JSON_VALUE(t1.data, CONCAT('$[', l.depth + 1, ']')) AS data
    FROM t1, labels as l
    WHERE JSON_LENGTH(t1.data) - 1 > l.depth
)
SELECT data FROM labels;


Share : facebook icon twitter icon

How do I limit a query with distinct values in MariaDB


By : Jacob Manuel Cuevas
Date : March 29 2020, 07:55 AM
seems to work fine I have a table like this: , Try this approach:
code :
select *
from Table1 as T1
join (
  select distinct num
  from Table1 
  order by num
  limit 2 ) as T2
on T1.num = T2.num  
  ;

select 2 fields and return a sorted array with their distinct values


By : Nam Vo
Date : March 29 2020, 07:55 AM
Hope this helps You need to $group our documents and use the $push accumulator operator to return an array of "a" and "b" within the collection.
In the $project operator you use the $setUnion operator to filter out the duplicates.
code :
db.coll.aggregate(
    [
        { "$group": { 
            "_id": null, 
            "a": { "$push": "$a" }, 
            "b": { "$push": "$b" } 
        }}, 
        { "$project": {
            "_id": 0, 
            "merged": { "$setUnion": [ "$a", "$b" ] } 
        }} 
    ]
)
{ "merged" : [ 3, 2, 0, 1 ] }

Select Distinct Array Values from Postgresql Table


By : Jorge Rebé Martín
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I have a PostgreSQL database table which looks like this , In Postgres, you can use array functions like;
code :
select distinct unnest(category) as nestCategory from content

select distinct values from JSON using django-mysql


By : animemina
Date : March 29 2020, 07:55 AM
Hope that helps I'm using this library and my model looks like this: , you can use values_list method
code :
PhoneTests.objects.all().values_list('data__mOfficerCode').distinct()

MariaDB/MySQL select query replace json array of ids into concatenated values


By : GBJpq17
Date : March 29 2020, 07:55 AM
Hope that helps I'm not too familiar with database queries, but I would like to do the following: in a table, I have stored a column of tag ids like: [1,6,8] and I wish to return "Music, Dance, Pop" for example in my select query. ,
and I'm not allowed to modify it
code :
SELECT
    p.id,
    p.name,
    GROUP_CONCAT(t.name) AS tag_names
FROM posts p
LEFT JOIN tags t
    ON JSON_SEARCH(p.tag_ids, 'one', t.id) IS NOT NULL
GROUP BY
    p.id,
    p.name;
Related Posts Related Posts :
  • Moving data between tables on a specific condition
  • Prime Number using GOTO statement in PL/SQL
  • How to get Check Out time for employee who is working on night shift
  • SQL Using Multiple Agregate Functions At Once
  • Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated
  • Insert automatically generated primary key into another table when inserting
  • Execute Package Utility (dtexecui) not installed after installing SQL Server 2017
  • Oracle Query: How to compare two column of different table having comma separated values
  • COUNT with LEFT JOIN
  • How do I update this query so as to use listagg instead of wm_concat?
  • Sampling for a SQL Database
  • Getting oldest record from SQL Server using T-SQL
  • How can Devise users create an invitation for others to accept and start and able to finish invitation
  • Create a group by and display a sequence number?
  • How to check if a "Send Query" is over?
  • Why is no data being returned
  • How to get records by using foreign key in one table using primery key another table in visual basic 6.0
  • what is the meaning of this code ..[select @a:=password(?)]
  • How avoid using group by SQL ORACLE
  • Wrong number or types of arguments error using cursors with parameters
  • Simple Subquery Statement, receiving an error
  • sql extract rightmost number in string and increment
  • How to convert column with Rank Value into rows in oracle
  • SQL Query to convert Particular Rows into Column
  • Why is PostgreSQL 11 optimizer refusing best plan which would use an index w/ included column?
  • SELECT Column Value based on Subselect if record exists
  • Autocast from "YYYY-MM" varchar to date in PostgreSQL
  • SQL - For each ID, values in other columns should be repeated
  • How to Replace multiple space in table column
  • How to convert unix timestamp to date with time zone?
  • How can I filter JSON column in Postgresql?
  • How to assign SQL query result to variable?
  • difficulties to fetching data from table
  • Merge Function limitation in snowflake
  • Display all the columns in employees table along with average salary of each dept
  • IF ROWS IS ALL EQUAL
  • Can anyone help me to understand why oracle sql length function accepting numeric values or number datatype?
  • Snowflake - Check if 1st 3 Characters of string are letters
  • Most Elegant Way to Populate Nulls from Reference Table
  • Can anyone write this query without a cursor?
  • SQL - Incremental insert using SSIS?
  • Why is there such a big difference between performance of those two queries?
  • how would SQL Server interpret this where clause?
  • How to query with multiple conditions that won't match on just a single row
  • HANA SQL IsDate() equivalent or to_date function that takes invalid data?
  • How do I convert this query into one using joins/unions in SQL Server 2016?
  • Increment SQL Server variable across multiple SELECT queries
  • Delete rows where all columns (except ID) are NULL
  • What is wrong with my joining derived table query?
  • Converting a table to a different form
  • Only selecting certain rows based on calculated value
  • Extra quotes doing SQL insert from Perl to CSV. If I try to remove them, I get no quotes. I need single quotes as in &qu
  • Create table from table of column names and data types
  • SQL Loop through months
  • Making sure Identity column doesn't allow change in another value
  • Oracle SQL how to select (or include data) if it doesn't exist sometimes?
  • Select MAX between 2 similar ID
  • How to fix "is invalid in the select list because it is not contained in either an aggregate function or the GROUP
  • Is there a reason this procedure is not updating my Table?
  • Return Most Recent Record group by 2 ID fields
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk